locked
SQL Trigger RRS feed

  • Question

  • Hi folks, I was curious if there is a possible way or a script to create multiple update triggers at the same time for each table in a database for an update field using the tables name as part of the name like for the Accounts table Trigger AccountsUpdate for the Address table AddressUpdate trigger?  The field that would be updated is DateUpdated.  Or do you have to create each trigger for multiple tables individually?  I'm fearing the worst but hoping for the best.
    Wednesday, August 30, 2017 10:18 PM

Answers

  • What is the point? Create a script manually for the tables and press F5 and you will get multiple triggers at the same

    time

    BTW


    This is common scenario is auditing of data in a table using triggers. But the example below shows code that uses OUTPUT clause in UPDATE statement to insert rows into an audit table.
     
    create table t ( i int not null );
    create table t_audit ( old_i int not null, new_i int null );
    insert into t (i) values( 1 );
    insert into t (i) values( 2 );
     
    update t
       set i  = i + 1
    output deleted.i, inserted.i into t_audit
     where i = 1;
     

     
    select * from t;
    select * from t_audit;
     
    drop table t, t_audit;
    go

     It is a powerful feature that enables you
     to eliminate use of triggers in some cases or send results to client as 
    part of the data modification operation efficiently.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Kapil.Kumawat Thursday, August 31, 2017 5:23 AM
    • Marked as answer by Paul Bill Thursday, August 31, 2017 6:38 PM
    Thursday, August 31, 2017 5:19 AM