Trigger to trace DML stmts in a table
-
Dear All,
I need to trace all UPDATE,INSERT and DELETE of a table(STAFF table) using Trigger and store these information in a trace table.
While INSET.DELETE,UPDATE a value to the STAFF table that same records or same SQL Statements should store in my Trace table.
Please give suggestion, how I can trace all these activates using triggers
Is any other way other than triggers to Trace DML information ?
Regards
Mathew
-
Mathew,
This should be pretty easy. After creating your Trace table (sometimes called an Audit table?), create a trigger for your subject table:
Create trigger MyAuditTrigger
FOR insert
AS
INSERT into TraceTable (Col(1), Col(2), Col(3), Col(n)ActionDate) values
Select i.Col(1), i.Col(2), i.Col(3), Col(n), GetDate() from Inserted i
Look in Books On Line for good examples of other triggers for the update and delete process.
Elliott
-
Dear
Thanks for your reply.
I have one field in the audit table and that table I planed to put all values(select * from inserted) .
Now your suggestion I can put only one values to one field. But need to store all values of inserted table to one field.
Pls advice me
Mathew
-
You will have to write a select statemen to concatinate it. And if you have defauls on columns it won't tell you if they actuall populated the column with the default or if it was the defaul that did it. What is your goal here? What do you hope to get out of this?
-
Dear
My object is , if a table have 10 columns and these 10 columns inserted, I need to get all values in my trace table.( Select * from Inserted).
If I can concatenate the contents of a row in to one variable, I have to insert this variable to my trace table
Help me on it
Mathew
-
Sorry, I just think you are making a mountain here out of somethin normally done like this
I have a table
CREATE TABLE My_Data (
Data_ID int identity(1,1) not null primary key,
Data_Value varchar(20) not null,
Data_Date datetime not null DEFAULT GETDATE(),
Data_Meaning tinyint not null
)
And say I want to audit change, I create a second table for data auditing like so.
CREATE TABLE My_Data_Audit (
Audit_Date datetime not null DEFAULT GETDATE(),
Data_ID int not null,
Data_Value varchar(20) not null,
Data_Date datetime not null,
Data_Meaning tinyint not null
)
The I add the following to capture the changes and deletes only
CREATE TRIGGER Audit_My_Data ON dbo.My_Data
FOR UPDATE, DELETE
AS
SET NOCOUNT ON
INSERT
dbo.My_Data_Audit
(Data_ID, Data_Value, Data_Date, Data_Meaning)
SELECT * FROM deleted
GO
Now here is the thing, a data row cannot exist unless it was inserted so in and of itself it is the audit (except allowance for the datetime of the occurrance which is up to you if you resolve to the table or want to add an audit for inserts). Also I wouldn't take the data from inserted on UPDATES because either the current record in the min table or the next record in the audit table are the detail for that timeframe. Additionally you cannot generally withou a lot of effort capture failed items becase the rollback will undo it as well. Note also, I am a fan of namin your columns in a SELECT statement and never using * but in this case I usually make an exception as sometimes it is easy to overlook the audit trigger and forget to duplicat column changes to the audit table, using * will throw an error about column difference when you try.
Lastly, if you are tryin to put all items in one column really is overkill, build your app or use reporting servies to help you work the data later.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply