How to write one trigger to all table to SQL Server 2008

Question

I wrote a trigger which fires when update on Employee table.

When trigger fires it insert new record to Transact table.

ALTER TRIGGER [PROJECTS].[after_updateEmployee] ON [PROJECTS].[Employee]
AFTER  UPDATE
AS DECLARE @tablen varchar(30) , @modyfire  varchar(15),@modyfied_date datetime,@columname   varchar(20),@action varchar(6)
SET @tablen ='Employee'
set @modyfire =(SELECT SYSTEM_USER)
set @modyfied_date =(select CURRENT_TIMESTAMP)
SET @columname = (SELECT TOP 1 E_id  FROM PROJECTS.Employee ORDER BY RV DESC)
set @action ='UPDATE'
BEGIN
INSERT INTO PROJECTS.Transact values(@tablen,@modyfire,@modyfied_date,@columname,@action)
END

I have more tables in my database.So is there any way to use this trigger for all tables in my database.

Thank You.

Triggers are defined on a table - there's no concept of something like global triggers. If you want a trigger on 10 tables, you'll have to write 10 trigger - one on each table. — marc_s
– marc_s, Commented Nov 16, 2013 at 12:23
If your triggers follow a pattern - which I assume yours do - you should be able to generate a script by running select statements over the information_schema and then applying the triggers automatically (by running the script). — db9dreamer
– db9dreamer, Commented Nov 16, 2013 at 16:58
Adding to previous comments - you can automate it for newly created tables by creating a DDL trigger that will add appropriate trigger to new table. — Endrju
– Endrju, Commented Nov 16, 2013 at 20:07
There are two big warning signs when it comes to SQL Server triggers. The first is if the trigger assumes that only a single row has been inserted/updated/deleted. The second is if the trigger doesn't contain any references to the inserted and/or deleted pseudo-tables. This trigger appears to have both issues. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 27, 2013 at 8:01

Community · Accepted Answer · 2017-05-23 11:47:14Z

Based on this answer you can try the following:

Create new type as a table which contains object's id and table:

create type table_rows as table
(
    row_id      uniqueidentifier not null,  -- deleted object id
    table_name  varchar(50) not null        -- object's table
);

This function logs every deleted object in auto_log table:

create procedure mark_as_deleted(@r as table_rows readonly) as
begin
    insert into auto_log (id, row_id, table_name, t_deleted, editor)
        select NEWID(), row_id, table_name, getdate(), current_user from @r
end

And finally the trigger which must applied to each table:

create trigger on_delete_object on [dbo].[object]
instead of delete as
begin
    declare @table table_rows

    insert into @table
        select id, object_schema_name(parent_id) + '.' + object_name(parent_id)
        from deleted
        join sys.triggers on object_id = @@PROCID

    -- Do what ever you need with deleted data
    exec mark_as_deleted @table
end

The advantage of this approach is that all the triggers will be familiar and the code used to work with deleted data is placed in the separate procedure

Collectives™ on Stack Overflow

How to write one trigger to all table to SQL Server 2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related