Simple question on triggers
-
Hi,
When using a trigger on update of a row, what is the best practice regard inserting an audit record. In our trigger below, what is the easiest way to change it to audit an update?
Thanks
Rob Cooper
CREATE TRIGGER trig_sord_audt
on sord
FOR INSERT
AS
IF UPDATE (status)
BEGIN
INSERT INTO sord_audt (tkey,u_version,last_updated,last_upd_user,dt_created,user_created,level_1,level_2,activity_type,customer,ref_no,status,employee)
select newid(),
'!',
last_updated,
last_upd_user,
last_updated,
last_upd_user,
level_1,
level_2,
'NULL',
customer,
ref_no,
status,
last_upd_user
from inserted ins
END
-
Where you have FOR INSERT change to FOR UPDATE. The inserted table is present with UPDATE triggers. It represents the new values for the rows changing. The deleted table is also present and it represents the old values for the rows changing.
K. Brian Kelley
@kbriankelley -
as bkelly suggested change for insert to for UPDATE.
And you also want to change your from clause to be from Deleted, not INSERTED. If you do it the way you have it the Record in the primary table, and the record in the audit table will be the same.
-
This depends... if you are capturing the new values, you want inserted. If you are capturing the old values, you use deleted.
K. Brian Kelley
@kbriankelley -
OR if you want BOTH , which is usually the case
then execute the insert twice (one for each table of course )
* Noel -
As someone said to me... why not just do a single insert with a union all
. -
Because of the architecture is unknown ... as of this momment
It was not defined (yet) how does multiple record changes is going to be recorded. ex: Do they need to be tied up to a transaction ID, do you need to have separared Audit ID for each record affected, ... etc
If none of the above is needed then just as Remi said... use a union all.
* Noel -
If they are going in the same table and coming from the same table, I see no reason to do 2 inserts. There must be a way to have 2 selects that fetch the required data with the same amount of columns (since null can be selected
). -
What I am trying to say is that the process can be more complex than doing just a union all. If it is just a matter of purely recording what was there before and after then union all will suffice if you need to record by pairs then you need other query handling stuff, if you need to record by batch then you need different handling stuff, if you need to record more metadata you need other query handling stuff.... did that make sense ?
* Noel -
Yup. But I'm not saying the a word again
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply