Friday, February 24, 2012

Accessing which data caused a trigger to fire

Hi.

I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?

Best Regards
Jens Christian Andersen.There are 2 pseudo-tables available in trigger code: "deleted" and
"inserted". When a trigger is fired by an UPDATE statement, the "deleted"
table contains images of the rows before the update and the "inserted" table
contain the row images after the update. In your update trigger code, you
can do something like:

INSERT INTO Y.dbo.B (Column1, Column2)
SELECT Column1, Column2
FROM inserted

In a trigger fired by a DELETE statement, the "deleted" table contains the
rows just deleted and the "inserted" table is empty. Similarly, when a
trigger is fired by an INSERT statement, the "inserted" table contains the
rows just inserted and the "deleted" table in empty. See the Books Online
for details.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"jcandersen" <jca@.dse.dkwrote in message
news:1172660503.230464.291380@.8g2000cwh.googlegrou ps.com...

Quote:

Originally Posted by

Hi.
>
I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?
>
Best Regards
Jens Christian Andersen.
>

|||On 28 Feb., 14:07, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

There are 2 pseudo-tables available in trigger code: "deleted" and
"inserted". When a trigger is fired by an UPDATE statement, the "deleted"
table contains images of the rows before the update and the "inserted" table
contain the row images after the update. In your update trigger code, you
can do something like:
>
INSERT INTO Y.dbo.B (Column1, Column2)
SELECT Column1, Column2
FROM inserted
>
In a trigger fired by a DELETE statement, the "deleted" table contains the
rows just deleted and the "inserted" table is empty. Similarly, when a
trigger is fired by an INSERT statement, the "inserted" table contains the
rows just inserted and the "deleted" table in empty. See the Books Online
for details.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"jcandersen" <j...@.dse.dkwrote in message
>
news:1172660503.230464.291380@.8g2000cwh.googlegrou ps.com...
>

Quote:

Originally Posted by

Hi.


>

Quote:

Originally Posted by

I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?


>

Quote:

Originally Posted by

Best Regards
Jens Christian Andersen.


Thanks, this is just what I needed.

No comments:

Post a Comment