May 8, 2019 at 3:05 pm
So I have an IUD trigger on a table with quite a few columns. (Don't ask about normalisation that's another issue).
My code is …
INSERT INTO auditTable (a,b,c … k)
SELECT a,b,c...k FROM inserted
Later I repeat the exact same for the table deleted.
I was trying to think of a way to avoid repeating the same code block with just a different table name (inserted / deleted). There's nothing wrong with it, it's just that it's a repeat of a (very nearly) duplicate chunk but I can't see a better way. If anyone can, I'd be grateful for your ideas.
Thanks
May 8, 2019 at 8:20 pm
auditing inserts is "excessively repetitively redundant", i would only audit updates and deletes, as you have the original data in the table, if no updates occurred. do you REALLY need two copies of every row, plus updates and deletes?
anyway. i think what you want to do is to use a query to generate your trigger for each table.
since the columns of each table vary, you want to use metadata to generate statements.
for example this gives a concatenated list of columns.
SELECT DISTINCT
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + QUOTENAME(name)
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
This code is a half completed example of generating a trigger example
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH MyCTE
AS
(
SELECT DISTINCT
SCHEMA_NAME(schema_id) AS SchemaName,
t.name AS TableName,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + QUOTENAME(name)
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
)
SELECT
'CREATE TRIGGER TR_'
+ TableName
+ '_IUD ON '
+ QUOTENAME(SchemaName) +'.' + TableName
+ '
FOR INSERT,UPDATE,DELETE
AS
INSERT INTO [Audit].' + TableName
+ '(' + + ')
SELECT ' + Columns + '
FROM INSERTED'
FROM MyCTE
Lowell
May 9, 2019 at 5:08 pm
There's really not a better way. You'll need to repeat the column list for both the inserted and deleted tables for any method you use.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 10, 2019 at 10:08 am
Thanks Scott. That was the crux of the question. It seems inelegant repeating the same code block so I just wanted to be sure I hadn't missed a trick.
Thanks Lowell. Good point I hadn't considered about auditing inserts. And useful code if I need to gen a series of triggers in the future.
Appreciate you both taking the time!
🙂
May 14, 2019 at 12:26 pm
If you need to track the date of an insert, and the table doesn't already have a field from which that can be derived, that's a case for Insert trigger.
As for combining the code into a single statement, you could do a UNION ALL:
Insert AuditTable(....)
SELECT ....
FROM inserted
UNION ALL
SELECT ....
FROM deleted?
Of course, this gives you two rows for an update.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply