Trigger including query data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger including query data

Author

Topic

jocast
Starting Member

8 Posts

Posted - 2010-09-02 : 19:53:15

Hello I am doing some test on the trigger. I need to log every insert update or delete. In the log i need to include some data from the insert statement. is it possible? Or is possible to log the query used?

This is what i tried and it gave me an error
Msg 4104, Level 16, State 1, Procedure prueba, Line 5
The multi-part identifier "testtable1.reference" could not be bound.

CREATE TRIGGER test
ON testtable1
AFTER INSERT, UPDATE, DELETE
AS
UPDATE logtest SET [action] = 'INSERT', Date= GETDATE(),Reference = testtable1.reference
go

Thank you for your hlp.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-02 : 20:19:44

http://msdn.microsoft.com/en-us/library/ms189799.aspx

pay special attention to the inserted and deleted virtual tables

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-03 : 02:51:09

all information are available in the inserted and deleted tables for your insert and update/delete operations

jocast
Starting Member

8 Posts

Posted - 2010-09-03 : 12:30:19

hello, i used this
CREATE TRIGGER test
ON testtable1
AFTER INSERT
AS
declare @ref varchar(20)
set @ref = (select ref from inserted)
insert into logtest('INSERT',GETDATE(),@ref)
go

is there a way to log the actual query used for the insertion?

thank you

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-03 : 14:26:33

use a server side trace for that.

by the way, your trigger will fail if more than one record is inserted in the batch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources