Triggers - It cannot be this difficult

[Simon]

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

[John Bell]

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

[Simon Aarons]

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!

[Simon Hayes]

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

[John Bell]

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!

[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).