January 4, 2013 at 1:07 pm
Hi,
I am trying to write a generic trigger that I can add to tables in my database (if they follow the rule of having a primary key as column 1). The purpose of the trigger is to insert a record into a table called RecordHistory.
The trigger uses TableNames table to grab the id of current table. All the tables listed in [TableNames] have a primary key with a unique name e.g. Branches.BranchID, Departments.DepartmentID. That means I have customize the name of the primary key column in the trigger (example below).
Is there a way to generically specify the primary key column of a table? I've done quite a bit of searching and I haven't found anything.
Thank you for your time.
In the code below the trigger is on the table [AddressTypes] and the primary key is [AddressTypeID]. I would like to replace [AddressTypeID] with an alias that would make the trigger work for all tables.
CREATE TABLE [dbo].[RecordHistory](
[RecordHistoryID] [bigint] IDENTITY(1,1) NOT NULL,
[EntityNameID] [int] NOT NULL,
[EntityID] [int] NOT NULL,
[ActionType] [char](1) NOT NULL,
[Change] [xml] NULL,
[Stamp] [datetime] NOT NULL,
[UserID] [int] NOT NULL)
CREATE TABLE [dbo].[TableNames](
[TableNameID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](100) NOT NULL)
INSERT INTO TableNames (TableName) VALUES ('AddressTypes')
INSERT INTO TableNames (TableName) VALUES ('Branches')
INSERT INTO TableNames (TableName) VALUES ('Departments')
INSERT INTO TableNames (TableName) VALUES ('Addresses')
CREATE TABLE [dbo].[AddressTypes](
[AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
[ShortDesc] [varchar](15) NOT NULL,
[Description] [varchar](100) NOT NULL)
ALTER TABLE AddressTypes WITH NOCHECK
ADD CONSTRAINT PK_AddressTypes
PRIMARY KEY CLUSTERED (AddressTypeID)
CREATE TRIGGER [dbo].[AddressTypes_RecordHistory]
ON [dbo].[AddressTypes]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @InsertedID int
,@DeletedID int
,@EntityNameID varchar(50)
SET @EntityNameID = ( select TableNameID from TableNames WHERE TableName = (SELECT OBJECT_NAME(parent_obj) FROM sysobjects WHERE id = @@PROCID) )
SET @InsertedID = (SELECT TOP 1 AddressTypeID from INSERTED)
SET @DeletedID = (SELECT TOP 1 AddressTypeID from DELETED)
IF @InsertedID > 0 and @DeletedID > 0
INSERT INTO RecordHistory (EntityNameID, EntityID, ActionType) SELECT @EntityNameID, AddressTypeID, 'U' FROM INSERTED
ELSE
BEGIN
IF @InsertedID > 0 and @DeletedID is null
INSERT INTO RecordHistory (EntityNameID, EntityID, ActionType) SELECT @EntityNameID, AddressTypeID, 'I' FROM INSERTED
ELSE
INSERT INTO RecordHistory (EntityNameID, EntityID, ActionType) SELECT @EntityNameID, AddressTypeID, 'D' FROM DELETED
END
END
January 4, 2013 at 2:00 pm
There isn't a way to do exactly what you are asking. Have you looked at CDC? It would be far less overhead than what you are proposing here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2013 at 2:37 pm
rightfield (1/4/2013)
I am trying to write a generic trigger that I can add to tables in my database (if they follow the rule of having a primary key as column 1).
In my experience, generic triggers are slow and complex. What can work well is to write a procedure that uses dynamic SQL and the catalog views to generate triggers for each table.
Or CDC.
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
January 4, 2013 at 3:06 pm
Thanks Sean. I didn't know anything about CDC so I looked at http://technet.microsoft.com/en-us/sqlserver/gg313769.aspx.
It cannot be implemented in the environment now but it was interesting. Thank you.
January 4, 2013 at 3:12 pm
Dynamic SQL. Interesting solution, thank you.
January 5, 2013 at 7:57 am
CELKO (1/4/2013)
...
If you want to keep an audit, then use an external tool for it.
...
Why using an external tool? There is an audit function within SQLServer too. It works on database and server level and you can nearly audit anything with it: http://msdn.microsoft.com/en-us/library/cc280526%28v=sql.105%29.aspx
Depending on the background of tracing changes the "change data capture"-function might serve a solution. http://msdn.microsoft.com/en-us/library/bb522489%28v=sql.105%29.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply