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]

Re: Triggers - It cannot be this difficult

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/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp?frame=true

John

"Simon" <aaronss@the-mdu.com> wrote in message
news:f526ea06.0 308270235.620f7 95@posting.goog le.com...[color=blue]
> 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[/color]

[Simon Aarons]

Re: Triggers - It cannot be this difficult

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]

Re: Triggers - It cannot be this difficult

aaronss@the-mdu.com (Simon) wrote in message news:<f526ea06. 0308270235.620f 795@posting.goo gle.com>...[color=blue]
> 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[/color]

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]

Re: Triggers - It cannot be this difficult

Hi

The example without the line

IF (COLUMNS_UPDATE D() & 14) > 0

will do this.

John

"Simon Aarons" <aaronss@the-mdu.com> wrote in message
news:3f4c96d1$0 $62079$75868355 @news.frii.net. ..[color=blue]
> 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![/color]

[jmj]

Re: Triggers - It cannot be this difficult

aaronss@the-mdu.com (Simon) wrote in message news:<f526ea06. 0308270235.620f 795@posting.goo gle.com>...[color=blue]
> 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[/color]

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