October 30, 2007 at 10:13 am
I am working on a trigger for a table I would like to audit. I am quite new to triggers. My trigger so far will 'capture' just one row per firing of the trigger and place it into the audit table. I originally thought this would be fine but I discovered that there are times when during one database transaction multiple rows may be deleted or updated or inserted. I do not know how to modify my trigger such that it will 'capture' all rows instead of simply the top 1. I thought about putting the COUNT of the DELETED table into a variable so I would know how many rows were affected by the transaction, however I do not know how to loop through them. Am I on the right track? Do you have any advice on this? I have included by trigger below. Thank you.
----
CREATE TRIGGER UDA_BillOfMaterials_A_TRX ON UDA_BillOfMaterials_A FOR INSERT, UPDATE, DELETE AS
BEGIN
DECLARE @Iobjectkey int,
@Dobjectkey int,
@ExecStr varchar(50),
@Qry nvarchar(255)
/* START QUERY CAUSING TRIGGER */
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
/* END QUERY CAUSING TRIGGER */
SELECT TOP 1 @Dobjectkey = object_key FROM DELETED
SELECT TOP 1 @Iobjectkey = object_key FROM INSERTED
INSERT INTO TRX_UDA_BillOfMaterials_A
(audit_trx_id,
audit_trx_time,
object_key,
site_num,
last_modifier_key,
last_modified_time,
xfr_insert_pid,
xfr_update_pid,
trx_id,
AllowPimaryBomOutput_I,
AlternateID_I,
SubCategory_S,
UpperTolerance_F,
LowerTolerance_F,
BOMQty_F,
triggered_time,
query,
sql_user)
SELECT D.audit_trx_id,
D.audit_trx_time,
D.object_key,
D.site_num,
D.last_modifier_key,
D.last_modified_time,
D.xfr_Insert_Pid,
D.xfr_Update_Pid,
D.trx_id,
D.AllowPimaryBomOutput_I,
D.AlternateID_I,
D.SubCategory_S,
D.UpperTolerance_F,
D.LowerTolerance_F,
D.BOMQty_F,
GETDATE(),
@Qry,
(select system_user) FROM UDA_BillOfMaterials_A D WHERE D.object_key = @Iobjectkey
INSERT INTO TRX_UDA_BillOfMaterials_A
(audit_trx_id,
audit_trx_time,
object_key,
site_num,
last_modifier_key,
last_modified_time,
xfr_insert_pid,
xfr_update_pid,
trx_id,
AllowPimaryBomOutput_I,
AlternateID_I,
SubCategory_S,
UpperTolerance_F,
LowerTolerance_F,
BOMQty_F,
triggered_time,
query,
sql_user)
SELECT D.audit_trx_id,
D.audit_trx_time,
D.object_key,
D.site_num,
D.last_modifier_key,
D.last_modified_time,
D.xfr_Insert_Pid,
D.xfr_Update_Pid,
D.trx_id,
D.AllowPimaryBomOutput_I,
D.AlternateID_I,
D.SubCategory_S,
D.UpperTolerance_F,
D.LowerTolerance_F,
D.BOMQty_F,
GETDATE(),
@Qry,
(select system_user) FROM DELETED D WHERE D.object_key = @Dobjectkey
END
October 31, 2007 at 1:13 am
The problem is in the lines
SELECT TOP 1 @Dobjectkey = object_key FROM DELETED
SELECT TOP 1 @Iobjectkey = object_key FROM INSERTED
There can be multiple lines in the inserted and deleted tables, if multiple rows were affected by the statement that fired the trigger. Those assignemnts only get a single row.
May I suggest something like the following.
INSERT INTO TRX_UDA_BillOfMaterials_A
(....)
SELECT ... ,
GETDATE(),
@Qry, system_user
FROM inserted -- no need for a where clause. inserted contains all the rows that were affected by an insert or update
INSERT INTO TRX_UDA_BillOfMaterials_A
(....)
SELECT ... ,
GETDATE(),
@Qry, system_user
FROM deleted -- no need for a where clause. deleted contains all the rows that were affected by an update or delete
Couple points. For an insert or delete that's fine. You get the inserted rows from inserted and the rows that were deleted from deleted.
With an update however, the old value of the rows is in deleted and the new value in inserted. You're getting a double entry into the audit table. Is that what you want?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2007 at 6:34 pm
Gail's given great advice. I'd follow what she's listed.
November 7, 2007 at 7:34 pm
I see this quite often... people auditing INSERTs, I mean... why would you double (or more) the storage requirements by auditing INSERTs? Audits are supposed to keep track of changes to the original data and who/what made the change. If no one changes the data in a row, there should be no audit rows about that row. If you insert a new row into a table, all the original data is inserted at that time... lemme say that again... the row you just inserted contains all of the original data... nothing in that row has changed since it was just inserted... you don't need to audit that new data.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2007 at 11:04 pm
Depends on the business requirements. In one of the systems I maintain, we have to (for legal purposes) have a record of who inserted the rows and when (along with other metadata about how), who changed it and who deleted it.
We could have added the date and user inserted columns to every table, but is simpler just to stick everything in the audit table.
We're getting audited a lot recently and the auditors get very suspicious when they see update and delete entries in the audit trail without an insert
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2007 at 11:12 pm
Heh... that's part of the point, Gail. If business users write it as a requirement, they need to be educated because it's wrong. If auditors get nervous because they don't see INSERTs in the audit table, it's because they don't really know what they're doing.
But, I'll agree... many DBA's don't get any say so in those things...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply