Need Help For SQL Trigger

[gr33nc0d3]

Dear all mmbers and VB professionals,
=======================================
i need help in SQL Trigger :
i'm using VB 6.0 and MSSQL 2000
=======================================
Tables :
1. Stock
- code varchar(12) --->barcode
- names varhcar(50)
- qty int

2. Sale
- dos datetime --->date of sale
- code varchar(12) --->barcode
- qty int
- Price money

3. Purchase
- dop datetime ---> date of purchased
- code varchar(12) --->barcode
- qty int
- price money

3a. Purchase_Log
- dop datetime ---> date of purchased
- code varchar(12) --->barcode
- qty int
- price money
=======================================
Conditions :
* Purchase
if code not found in stock then "Addnew" in stock table with a complete data (barcode,item name,qty)
else
if found then just update stock qty = qty + purchase.qty
also INSERT INTO table "Purchase_Log"

* Sale
same condition
=======================================
Thank you very much for your help.
=======================================

[si_the_geek]

Welcome to VBForums

Thread moved to the 'Database Development' forum - which is where you should always post SQL based questions (while SQL can be used in VB6, it is certainly not specific to VB6)

[gr33nc0d3]

thank you very much for helped me to move the thread...

[FunkyDexter]

What's your question? You've given us detail about what you're trying to do and it seems quite standard stuff but I don't understand what you're actually asking us. What are you stuck on?

[gr33nc0d3]

i'm sorry about my English ...
i stuck on trigger insert update delete

an example :
i buy something and i input it from Purchase Form.
normally for the existing item it will be updating field qty inside stock table

i stuck here :
when i input new item from input form, i want it automatically stored in stock table (as a new item)

also need help on update and delete ...

so far i never used trigger before ... just normal recordset and sql query ..

Thank you ....

[techgnome]

This isn't a trigger situation... triggers should be avoided if they can... what you're describing is business logic, and so should be wrapped up in a stored procedure...

-tg

[FunkyDexter]

Are you after the basics of how to create a trigger? If so you just use the CreateTrigger command or you can do it through management studio: just right click on the 'Triggers' node of the table you want to add the trigger to and select 'New Trigger'.

As TG said, though, triggers can be problematic. They tend to hide code, making it difficult to predict the results of an operation. They have their place but I agree with TG that this should be in a stored procedure or some other business logic layer.

[gr33nc0d3]

Stored Procedure ... ok ... thank all , i will try my best to figure it out ....
but still, i need a support from you all, i know i can get a lot of knowledge in this forum and also can learn a lot from here .....

Anyway ,. thx i'll be back after i figure it out .///

Thank you

[techgnome]

@gr33nc0d3 - if you're wondering why a trigger isn't appropriate... if the data doesn't exist... there's no data to update, therefore there is no trigger that can be fired off...

-tg

[gr33nc0d3]

OK, thank you all ...