Get new value from trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get new value from trigger

Author

Topic

Guestuser18
Starting Member

34 Posts

Posted - 2009-11-06 : 10:43:04

Hi guys

In my trigger I need to record an error message which contains the value which was just updated i.e. the value from the field the trigger is running on.
I thought it would be UPDATE.fieldname but that doesnt seem to work
In oracle its just :NEW.fieldname
I need the same thing but in t-sql
any help would be great

thanks

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 10:51:45

your previous value is in deleted.fieldname new in inserted.fieldname
does this answer your question?

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Guestuser18
Starting Member

34 Posts

Posted - 2009-11-06 : 10:56:10

Hi

I'm using

@V_ERRORMESSAGE VARCHAR(4000)
@V_newfield datetime

SET @V_newfield = inserted.field
SET @ErrorMge = 'Error ' + @V_newfield

and getting the error message:

Msg 4104, Level 16, State 1, Procedure PROCEDURE, Line 11
The multi-part identifier "inserted.field" could not be bound

ANy help would be great on this, why can it not be bound?

thanks so far.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-06 : 11:55:09

you store the value of inserted.field into a table variable like this from BOL


INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 11:59:58

yosiasz gives a good solution, that does not need a trigger

if you really need a trigger

@V_ERRORMESSAGE VARCHAR(4000)
@V_newfield datetime

SELECT @V_newfield = field
FROM @inserted

SET @ErrorMge = 'Error ' + @V_newfield

this will work correctly only if you update one row at a time

if you update more rows you need to add a where to SELECT, to filter for a case you are looking for

in your case I would suggest doing data validation in a procedure

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources