Trigger?? or some-other way.... 1

[jojo79]

I need help on a trigger that will update a tables value if the skew number is exists, if not, add it to the table. My information is below, I have tried many triggers, but all without results I am looking for. I just need a little help thinking outside the box.

tbl_inv
skew(nvarchar)
cases(int)
skids(int)

tbl_enter
id(int)
skew(nvarchar)
cases(int)
skids(int)
date(date)

Basically, When a record is inserted into tbl_enter it will fire a trigger that inserts skew(nvarchar),cases(int),skids(int) into tbl_inv. If skew already exists in tbl_inv it will add the inserted data to the current data.if not it will just add it to the table.

 

[jojo79]

Sorry, forgot to post example.


Example:
Before Data inserted into tbl_enter
tbl_inv
SKEW Cs. Sk.
FCA6850-01 50 75
28113-26000P 25 50
FCA4939-01 75 100

Inserted data into tbl_enter
FCA6850-01 10 20

After Data inserted into tbl_enter and trigger fires.
tbl_inv
SKEW Cs. Sk.
FCA6850-01 60 95
28113-26000P 25 50
FCA4939-01 75 100

 

[monksnake]

Try this and see if it works (called as a stored procedure after an insert into tbl_enter):

CREATE PROCEDURE tbl_enter_sp
   @skew_value nvarchar(10), 
   @cases int, 
   @skids int
AS
   IF (SELECT COUNT(*) FROM tbl_inv WHERE skew =
   @skew_value) = 0
   BEGIN
      INSERT INTO tbl_inv SELECT @skew_value, @cases,
      @skids
   END
   ELSE
   BEGIN
      UPDATE tbl_inv SET cases = cases + @cases, skids =
      skids + @skids WHERE skew = @skew_value
   END
go

<.

 

[SQLSister]

No that's a bad idea. You never ever under any circumstances want to create a trigger that can only process one record which calling that sp would do. This will create data integrity problems if you ever do a mass insert or update which I guarantee will happen at some time.

First, in a trigger you have two pseudotables available, inserted and deleted which contain the data that was inserted deleted or changed (old values in deleted and new ones in inserted)

Use these to get the information you want to place in the table. Then you do a simple update statement that updates any values that exist and followit with an insert statement to add any that do not exist inthe target table.

examples within the trigger on the first table call it table 1 (you can look up trigger syntax if you don't know it in BOL)

update table2
set field1 = field1 + i.field1
from table2 t join inserted i on t.idfield = i,idfield 

insert table1 (field1, field2, field3)
select i.field1, i.field2, i.field3 from inserted i
left join
table2 t on i.idfield = t.idfield
where t.idfield is null

Questions about posting. See faq183-874

 

[monksnake]

Thanks SQLSister, I understand totally and it makes sense. I'm just now learning specific rules about SQL, so I will make mistakes like that.

<.

 

[SQLSister]

monksnake, first you learn to make something work, then you learn how to do it better. Many people never learned that triggers do not operate one record at a time but on all records which were in the same transaction. The bad part is that if most data entry is done from the user interface, it may be a long long time before anyone discovers the problem and by then the data may be irreparably messed up. So triggers are something you need to be very careful to get right.

Some people get around this by adding a cursor to the trigger so that you can process one record at a time but this is another very dangerous thing to do. What happens when you do a mass update that affects a million records? You could have one transaction locking your system up for hours with a cursor in the trigger. So it is best when working with triggers to always use a set-based soution if at all possible.

Triggers are also hard to test. So what I usually do is create temp tables names #inserted and #deleted and fill them with sample data that will be inserted or changed ot updated from a trigger and then write the SQl using #inserted or #deleted rather than inserted or deleted and test (on dev of course, never consider testing a trigger on production). Once I'm satisifed that I'm getting the correct thing happening whether one or more records are in the transaction then I create the trigger using inserted and/or deleted instead of the temp table names.

Questions about posting. See faq183-874