469,097 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

Triggers - It cannot be this difficult

Hi

I am trying to produce an update trigger. I understand the concept of
delete and insert triggers without a problem. Unfortuantely, the
update triggers do not have particularly simple documentation in BoL.

So, can someone please explain to me, quite simply how I would produce
a trigger on the following:

I have table 1 which we'll call simon. In here are various columns and
rows. I also have table 2, called simon_a, my audit table.

Whenever anything is updated or deleted in simon, I want it sent to
the simon_a table. Delete, as above, is fine since it's conceptual but
help me out on the update one. I cannot seem to figure out how to get
the information from the table before it's updated.

As ever, champagne and beer for the successful answer.

With thanks

Simon
Jul 20 '05 #1
5 2436
Hi

The before image of your record(s) are held in the deleted "table"

The example "E. Use COLUMNS_UPDATED" in the "CREATE TRIGGER" topic in Books
Online shows a typical auding type trigger

http://msdn.microsoft.com/library/de...asp?frame=true

John

"Simon" <aa*****@the-mdu.com> wrote in message
news:f5*************************@posting.google.co m...
Hi

I am trying to produce an update trigger. I understand the concept of
delete and insert triggers without a problem. Unfortuantely, the
update triggers do not have particularly simple documentation in BoL.

So, can someone please explain to me, quite simply how I would produce
a trigger on the following:

I have table 1 which we'll call simon. In here are various columns and
rows. I also have table 2, called simon_a, my audit table.

Whenever anything is updated or deleted in simon, I want it sent to
the simon_a table. Delete, as above, is fine since it's conceptual but
help me out on the update one. I cannot seem to figure out how to get
the information from the table before it's updated.

As ever, champagne and beer for the successful answer.

With thanks

Simon

Jul 20 '05 #2
That's fine for specific columns which might be updated, but how is it
that you specify for all columns and insert the old data into the audit
table.

Simon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
aa*****@the-mdu.com (Simon) wrote in message news:<f5*************************@posting.google.c om>...
Hi

I am trying to produce an update trigger. I understand the concept of
delete and insert triggers without a problem. Unfortuantely, the
update triggers do not have particularly simple documentation in BoL.

So, can someone please explain to me, quite simply how I would produce
a trigger on the following:

I have table 1 which we'll call simon. In here are various columns and
rows. I also have table 2, called simon_a, my audit table.

Whenever anything is updated or deleted in simon, I want it sent to
the simon_a table. Delete, as above, is fine since it's conceptual but
help me out on the update one. I cannot seem to figure out how to get
the information from the table before it's updated.

As ever, champagne and beer for the successful answer.

With thanks

Simon


In an update trigger, the deleted table has the original rows, and the
inserted table has the modified rows:

create trigger tru_simon
on dbo.simon
for update
as
begin

insert into dbo.simon_a (col1, col2, audit_action)
select col1, col2, 'update - before image'
from #deleted

insert into dbo.simon_a (col1, col2, audit_action)
select col1, col2, 'update - after image'
from #inserted

end

See "Using the inserted and deleted Tables" in Books Online.

Simon
Jul 20 '05 #4
Hi

The example without the line

IF (COLUMNS_UPDATED() & 14) > 0

will do this.

John

"Simon Aarons" <aa*****@the-mdu.com> wrote in message
news:3f***********************@news.frii.net...
That's fine for specific columns which might be updated, but how is it
that you specify for all columns and insert the old data into the audit
table.

Simon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #5
jmj
aa*****@the-mdu.com (Simon) wrote in message news:<f5*************************@posting.google.c om>...
Hi

I am trying to produce an update trigger. I understand the concept of
delete and insert triggers without a problem. Unfortuantely, the
update triggers do not have particularly simple documentation in BoL.

So, can someone please explain to me, quite simply how I would produce
a trigger on the following:

I have table 1 which we'll call simon. In here are various columns and
rows. I also have table 2, called simon_a, my audit table.

Whenever anything is updated or deleted in simon, I want it sent to
the simon_a table. Delete, as above, is fine since it's conceptual but
help me out on the update one. I cannot seem to figure out how to get
the information from the table before it's updated.

As ever, champagne and beer for the successful answer.

With thanks

Simon


Triggers create two tables; INSERTED and DELETED. The deleted table
is the before image, the inserted table is the after image. On insert
or delete triggers only one table is populated, but with the update
trigger you have the old info (deleted) and new info (inserted) in
their respective tables.

Send the info from the deleted table to simon_a in either case
(updating or deleting).
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by raulgz | last post: by
1 post views Thread by anjana sisodia via SQLMonster.com | last post: by
1 post views Thread by Jeff Magouirk | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.