SQL Server Performance

!!!Triggers!!!

Discussion in 'SQL Server 2005 General Developer Questions' started by darkangelBDF, Sep 15, 2008.

  1. darkangelBDF Member

    Damn, I knew this day would come. I'm a plain old DBA who's never had to create a single trigger in her life thanks to the environments I've worked in up to now. Then it hit me today. I have to write 6 AFTER triggers for INSERT, UPDATE, DELETE on 6 tables within my database. I've never written a trigger before in my life and this is driving me a little insane.
    They want to have records written to seperate tables (already exist) for when an INSERT, UPDATE or DELETE takes place on any of the given fields. I include the code they sent me (apparently they took Oracle scripts and changed it a bit). Please help me as this is driving me nuts:CREATE
    OR REPLACE TRIGGER CRPDTA.F0101_PDSafter
    insert or delete or update OF ABAN8, ABALPH, ABAT1 on CRPDTA.F0101for
    each row-- declare variablesdeclarecmmd varchar2
    (64);begin
    -- set timezone to currentcmmd
    :='alter session set time_zone = dbtimezone';
    execute immediate cmmd;
    if inserting then
    if :)new.ABAT1 in('CQE', 'CQH', 'CQL', 'VQL') then
    -- write audit record for insert
    insert into CRPDTA.SC0101 (CZCKEY, CZEDSP, CZAN8, CZSCDT, CZATIM, CZADLJ, CZARTM) values
    ((select 'CS0101 ' || (TO_CHAR(localtimestamp, 'YYYY-MM-DD HH24.MI.SSXFF')) from dual), '0', :new.ABAN8, :new.ABUPMT, :new.ABUPMJ, '', '');
    end if;elsif updating
    then
    if :)old.ABAN8 != :new.ABAN8 or :eek:ld.ABALPH != :new.ABALPH or :eek:ld.ABAT1 != :new.ABAT1 or :eek:ld.ABUPMJ != :new.ABUPMJ or :eek:ld.ABMCU != :new.ABMCU) and :)new.ABAT1 in ('CQE', 'CQH', 'CQL', 'VQL') then
    -- write audit record for insert
    insert into CRPDTA.SC0101 (CZCKEY, CZEDSP, CZAN8, CZSCDT, CZATIM, CZADLJ, CZARTM) values
    ((select 'SC0101 ' || (TO_CHAR(localtimestamp, 'YYYY-MM-DD HH24.MI.SSXFF')) from dual), '0', :new.ABAN8, :new.ABUPMT, :new.ABUPMJ, '', '');
    end if;elsif deleting
    then
    if :)old.ABAT1 in('CQE', 'CQH', 'CQL', 'VQL') then
    -- write audit record for insert
    insert into CRPDTA.SC0101 (CZCKEY, CZEDSP, CZAN8, CZSCDT, CZATIM, CZADLJ, CZARTM) values
    ((select 'SC0101 ' || (TO_CHAR(localtimestamp, 'YYYY-MM-DD HH24.MI.SSXFF')) from dual), '0', :eek:ld.ABAN8, :eek:ld.ABUPMT, :eek:ld.ABUPMJ, '', '');
    end if;
    end if;end
    ;
  2. satya Moderator

    For such code examples SQL Server books online( BOL) is best resource for you that gives initial information on coding part, make sure to download and update your local copy of BOL.In your case even web has few resources to help:http://www.exforsys.com/tutorials/sql-server-2005/sql-server-ddl-triggers.htmlhttp://www.coderecipes.net/
  3. bhuvnesh_dba New Member

    CREATE TABLE dbo.Table_Audit
    (Table_name
    nvarchar(150) NULL,
    User_name varchar(50) NULL,DML_time
    datetime NULL) ON [PRIMARY]
    GO--------------------Create
    Trigger tr_track_DML_operationON
    working_table FOR
    INSERT,UPDATE ,DELETE AS INSERT INTO Table_Audit
    SELECT 'working_table',user_name(),getdate()---------------------create
    table working_table (name
    varchar(100))insert
    into working_table select '1'select * from Table_Audit
    Hi this is sort of solution you want.
    you need to create table_audit table for tracking and apply trigger on
    every table
    whenevr any DML operation will be performed on any table this table_audit will get one entry
    here i have taken a sample table (working table ) to test the scenario
    i hope this will help you [:)]

Share This Page