Generic Triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Generic Triggers

Author

Topic

augustin_p
Starting Member

21 Posts

Posted - 2002-04-15 : 05:02:34

Hello,
Is it possible to write a generic trigger that shall be invoked if any of the tables in a database gets updated, inserted or deleted. I do not want to write a trigger for each and every table

Thanks,
prasanna

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-15 : 07:49:31

Sorry, you have to write triggers for each table.

motokevin
Starting Member

36 Posts

Posted - 2002-04-15 : 18:23:03

You could write a trigger for each table, or you could just have SQL write each individual trigger for you. You just have to tell it how.

Think I'm kidding?

Here is the code to get you started:

Create Procedure sp_EvenBetterThanAGenericTrigger

As

Declare @TableName varchar(100)
Declare @KeyColumnName varchar(100)
Declare @ThisColumnName varchar(100)
Declare @DeleteTriggerProcedure varchar(100)
Declare @CreateTriggerProcedure varchar(8000)

Declare AllDatabaseTables Cursor Local Static
For
Select T.Table_Name,KCU.Column_Name
From Information_Schema.Tables T,Information_Schema.Key_Column_Usage KCU,Information_Schema.Table_Constraints TC
Where T.Table_Name=KCU.Table_Name
And KCU.Constraint_Name=TC.Constraint_Name
And Constraint_Type='PRIMARY KEY'
And T.Table_Name='Division'
Open AllDatabaseTables

Fetch First From AllDatabaseTables
Into @TableName,@KeyColumnName

While @@Fetch_Status = 0

Begin-----------------------------------------------------------------------------------------------------------

Declare ColumnNamesInThisTable Cursor Local Static
For
Select Column_Name
From Information_Schema.Columns
Where Table_Name=@TableName

Set @DeleteTriggerProcedure = '
Drop Trigger tr_TriggerFor' + @TableName + ';'
Set @CreateTriggerProcedure = '
Create Trigger tr_TriggerFor' + @TableName + 'test1
On ' + @TableName + '
AFTER Insert,Update,Delete
As

Declare @ThisRowCount Integer
Set @ThisRowCount=@@rowcount

Declare @InsertedCount Integer
Select @InsertedCount=Count(*)
From Inserted
Declare @DeletedCount Integer
Select @DeletedCount=Count(*)
From Deleted

If @ThisRowCount = 1
If (@InsertedCount > 0) And (@DeletedCount > 0)
Begin'
Open ColumnNamesInThisTable
Fetch First From ColumnNamesInThisTable
Into @ThisColumnName
While @@Fetch_Status = 0
Begin-----------------------------------------------------------------------------------------------------------
-- < Put Code Here For a single row Update. e.g. > --
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
Insert Into MyLogTable (TableName,ColumnName,OldValue,NewValue)
Select ''' + @TableName + ''',''' + @ThisColumnname + ''',Convert(Varchar,Deleted.' + @ThisColumnName + '),Convert(Varchar,Inserted.' + @ThisColumnName + ')
From Inserted,Deleted
Where Inserted.' + @KeyColumnName + '=Deleted.' + @KeyColumnName + '
And Not Inserted.' + @ThisColumnName + '=Deleted.' + @ThisColumnName + '
'
Fetch Next From ColumnNamesInThisTable
Into @ThisColumnName
End--------------------------------------------------------------------------------------------------------------
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
End
Else If (@DeletedCount=0)
Begin'
Fetch First From ColumnNamesInThisTable
Into @ThisColumnName
While @@Fetch_Status = 0
Begin-----------------------------------------------------------------------------------------------------------
-- < Put Code Here For a single row Insert. e.g. > --
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
-- single row insert code
'
Fetch Next From ColumnNamesInThisTable
Into @ThisColumnName
End--------------------------------------------------------------------------------------------------------------
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
End
Else If (@InsertedCount=0)
Begin'
Fetch First From ColumnNamesInThisTable
Into @ThisColumnName
While @@Fetch_Status = 0
Begin-----------------------------------------------------------------------------------------------------------
-- < Put Code Here For a single row Delete. e.g. > --
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
-- single row delete code
'
Fetch Next From ColumnNamesInThisTable
Into @ThisColumnName
End--------------------------------------------------------------------------------------------------------------
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
End
If @ThisRowCount > 1

If (@InsertedCount > 0) And (@DeletedCount > 0)
Begin'
Fetch First From ColumnNamesInThisTable
Into @ThisColumnName
While @@Fetch_Status = 0
Begin-----------------------------------------------------------------------------------------------------------
-- < Put Code Here For a multi-row Update. e.g. > --
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
-- multi-row update code
'
Fetch Next From ColumnNamesInThisTable
Into @ThisColumnName
End--------------------------------------------------------------------------------------------------------------
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
End
Else If (@DeletedCount=0)
Begin'
Fetch First From ColumnNamesInThisTable
Into @ThisColumnName
While @@Fetch_Status = 0
Begin-----------------------------------------------------------------------------------------------------------
-- < Put Code Here For a multi-row Insert. e.g. > --
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
-- multi-row Insert code
'
Fetch Next From ColumnNamesInThisTable
Into @ThisColumnName
End--------------------------------------------------------------------------------------------------------------
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
End
Else If (@InsertedCount=0)
Begin'
Fetch First From ColumnNamesInThisTable
Into @ThisColumnName
While @@Fetch_Status = 0
Begin-----------------------------------------------------------------------------------------------------------
-- < Put Code Here For a multi-row Delete. e.g. > --
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
-- multi-row Delete code
'
Fetch Next From ColumnNamesInThisTable
Into @ThisColumnName
End--------------------------------------------------------------------------------------------------------------
Set @CreateTriggerProcedure = @CreateTriggerProcedure + '
End'
Close ColumnNamesInThisTable
Deallocate ColumnNamesInThisTable

Exec(@DeleteTriggerProcedure)
Exec(@CreateTriggerProcedure)

Fetch Next From AllDatabaseTables
Into @TableName,@KeyColumnName

End-----------------------------------------------------------------------------------------------------------

Close AllDatabaseTables
Deallocate AllDatabaseTables
GO

Good Luck. Have Patience.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources