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.
| Author |
Topic |
|
Magnolia
Starting Member
2 Posts |
 Posted - 2007-11-27 : 19:34:15
|
| hi ,I need to capture the changes made to a table.I hav created a event_log table which captures the old values and new values.I do need to store the type of DML event excuted against the Db and the SQL query that changed the text?can someone help me??insert into Audit_table(EventTime,EventType,ServerName, DatabaseName, old_ContactID,New_ContactID, old_Contacttype, [New _Contacttype], UserName, CommandText) (select getdate(),null,@@SERVERNAME,db_name() +' User Name : '+SYSTEM_USER,deleted.ContactID,inserted.ContactID,deleted.ContactType,inserted.ContactType,user_name(),null FROM INSERTED FULL JOIN DELETED ON INSERTED.ContactID = DELETED.ContactID AND INSERTED.ContactType = DELETED.ContactType )Since i dont know how to solve i just hav inserted a null in the insert statement..the commandtext store the full query issued against the tablethankx in advanceRegards,Mag |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-27 : 20:00:02
|
| For insert statements, the inserted table will have rows.For update statements, the inserted and deleted tables will have rows.For delete statements, the deleted table will have rows.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Magnolia
Starting Member
2 Posts |
Posted - 2007-11-27 : 20:42:08
|
| how do i capture the whole DML statememt and store it in a column?Regards,Mag |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-27 : 20:48:48
|
| Perhaps you can run DBCC INPUTBUFFER against the current spid (@@SPID).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Capturing Event type in trigger