September 8, 2016 at 7:35 pm
Hi All,
I need your help in creating a dynamic script to create after insert,update triggers on a given table.
Trigger has to do following things:
Trigger has to fire for every insert or update made on specified table and insert SQL INSERT statements like INSERT INTO TABLENAME(Columns ...) VALUES(...) to queue_table(this table is common for all the tables), trigger should get columns from sys.columns and I need to create similar triggers for many tables
Create table Test
(
Col1 int,
Col2 nvarchar(500),
Col3 bit,
Col4 Datetime
)
CREATE TABLE queue_table
(
queue_id BigINT NOT NULL IDENTITY(1,1)
,sql_stmt NVARCHAR(MAX)
,is_moved BIT DEFAULT(0)
,create_date datetime DEFAULT (Getdate())
)
September 9, 2016 at 8:43 am
I have used the method here, with a bit of modification to achieve what I think you are trying to do:
https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/[/url]
Edit: sorry re-read your post and probably not entirely suitable!
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 9, 2016 at 11:57 am
yes, script provided in the link is not entirely suitable for my requirement.
September 12, 2016 at 3:45 pm
trigger should get columns from sys.columns and I need to create similar triggers for many tables
No, it should not! That's far, far too much overhead.
The trigger should not itself be dynamic. Instead, you should dynamically generate a static trigger. That is, the lookups to sys.colums, sys.etc, should take place only during trigger creation/modification. Yes, you will have to re-generate the trigger whenever the table schema changes. But still vastly better than the huge performance hit from a truly dynamic trigger.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 12, 2016 at 4:03 pm
ScottPletcher (9/12/2016)
The trigger should not itself be dynamic. Instead, you should dynamically generate a static trigger.
Seconded.
You can create a procedure that uses the system tables to generate and then run the CREATE TRIGGER statements, the triggers themselves should be as simple as possible and have no dynamic aspects at all.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply