Trigger not getting values from INSERTED
-
could you take a quick look and see if you see anything obvious. This code gets the RES_ID fine from inserted, but @ENTRY and @RES1 keep coming back NULL from the trigger (INSERTED) even though results_ext.entry and results_ext.res1 have the entered values in them.
The DECLARE statements are the same data types as the fields I added to the table results_ext.
Thanks for any help, Jim
*****************************************************
CREATE trigger results_ext_insert on results_ext
for insert
not for replication
as
if update(entry)
begin
DECLARE @RES_ID as char(20)
SELECT @RES_ID = RES_id FROM inserted
DECLARE @ENTRY as CHAR(15)
SELECT @ENTRY = entry FROM inserted
DECLARE @VAL1 as decimal(10,10)
SELECT @VAL1 = res1 FROM inserted
--USE variables in complex calcs not shown here
--Show values in variables by inserting into a table
update results_ext set res_fld3=@ENTRY where results_ext.res_id=@res_id
update results_ext set res_fld4=@VAL1 where results_ext.res_id=@res_id
update results_ext set res_fld5=@RES_ID where results_ext.res_id=@res_id
end
-
just a quick note: your trigger will fail if the INSERTED table has more than one row . you should change it to something like this:
if update(entry)
begin
update results_ext
set res_fld3 = INSERTED.entry,
res_fld4 = INSERTED.res1,
res_fld5 = INSERTED.RES_ID
FROM INSERTED
where results_ext.res_id=INSERTED.res_id
end
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Found the problem after talking to the vendor. The application I am using to update the values, inserts a blank record then UPDATES the values. My code did not look for updates!!!!
Thanks, Jim
-
To second what Lowell has posted, all triggers should be written to handle multiple row operations. The way you've written your trigger will only work with the last rows values in the trigger. Also, your post shows that there are complex calcs done on the variables before the final update. Are you sure that the disconnect is not there?
-
How would you ever get more than one row in the INSERTED table?
thanks, Jim
-
-
anytime you SELECT something, you technically could insert it into another table.
if you did SELECT TOP 5 NAME FROM sysobjects WHERE xtype='U', there's 5 rows right?
if INSERT INTO SOMETABLE(TableNames) SELECT TOP 5 NAME FROM sysobjects WHERE xtype='U',
that would insert 5 rows in a single operation into my sample table; if that table had a trigger, the trigger needs to consider that many rows are inserting, and not just one.
As a general rule, if you are declaring a variable to handle something in a trigger, you could probably re-write it to do it in a set based operation, and handle multi row logic correctly.
It might be that in your situation, you have an app or process that only works on one row at a time, so there's no real impact, but it's better to try and do it as a set based operation.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Wow... let's triple the work the server does... some vendors just don't get it...
Thanks for the feedback, Jim...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Maybe not as long as you are using the application. Some applications are written to do everything row by row (which is terrible... I should know that, the application we are using does the same on some tables
). But even if it's so, any trigger you create has to take into account the possibility that sometimes a multirow insert/update/delete happens! What if there is some problem you need to correct using QA? Most probably you (or your successor) will write an update that affects several rows at once, and there you go - trigger either fails or fires just on one row. You should write the trigger set-based, using JOINs to inserted and/or deleted tables. Not cursor, please!Write a test trigger and observe what happens if you run UPDATE statement on several rows. You will see that the trigger tables can hold any number of rows, not just one... and that you can for example insert all these rows (or some of them based on a condition) into log table with one statement.
Well, and to be honest, I suggest you don't create any triggers in production before you understand how they work. It is one of the easiest ways to mess up the data in a terrible way. Road to perdition. Consider yourself warned, and good luck!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply