create trigger grabs update/insert/delete queries

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)

create trigger grabs update/insert/delete queries

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-11-12 : 15:48:31

I need help creating a trigger that grabs update/insert/delete queries and inserts the entire query into a log database.

i have this and if my query uses text it works great.. but if I use parameters it shows the parameters and not the values.

I need to capture the tsql with the values(not so much the parameters)


CREATE TRIGGER LogSQL
ON dbo.M8_Customers
AFTER INSERT, UPDATE, DELETE
AS
INSERT INTO dbo.PB_Logs (language_event, parameters, event_info)
EXEC('DBCC INPUTBUFFER(@@SPID);');

sql query executed in vb.net application

UPDATE M8_Customers SET CustomerNumber = '" & Dispatch.txt_CustName_Customers.Text & "',CustomerName = @CustomerName,AllowDefault = @AllowDefault,Active = @Active WHERE custID = @custID

trigger inserts to PB_Logs this//

UPDATE M8_Customers SET CustomerNumber = '3 V Inc.g',CustomerName = @CustomerName,AllowDefault = @AllowDefault,Active = @Active WHERE custID = @custID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-12 : 19:19:24

You get the data values from the inserted and deleted trigger tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources