For the uninitiated on Oracle here's a short description of how an update trigger in oracle looks like:
CREATE OR REPLACE TRIGGER "RFSITE"."CNA_CALLTRIG"
after update on rfsite.cna_cell
for each row
if :old.CBCHD<>:new.CBCHD then
insert into cna_hist
- The main code of the trigger will be looped for each row. (due to portion highlighted in bold)
- for each row :old is a bound variable for all columns of old value.
- for each row :new is a bound variable for all columns of new value.
See how simple it is to store the history. Well not that easy in SQL Server. Here's how its different:
- In SQL server trigger is called ONCE will all the old values in deleted and new values in inserted .
- No option like 'for each row'
Heres a short description of how to do this:
- Declare column members as variables.
- Declare a cursor for select * from inserted inner join deleted on (
- loop through cursor pushing the values into your declared variables.
- Now that you have the variables do what you want with them.
Now that you have the theory down heres a short sample of how to do it.
@ProjectNEW varchar(255) ........etc
- DECLARE Item CURSOR FOR
deleted.RFCID as RFCIDOLD,
inserted.RFCID as RFCIDNEW,
deleted.Project as ProjectOLD,
inserted.Project as ProjectNEW ...... etc
deleted inner join inserted on
- Open Item
fetch Item into
- While @@Fetch_Status = 0
--You now have the required data in the new and old values same as oracle. Do as you like.
if @RequestedByOLD <> @RequestedByNEW
Print @RequestedByOLD +' Changed to ' +@RequestedByNEW
fetch Item into
@ProjectNEW .... etc
Of course you will need to end the loop close the cursor, deallocate it and end the trigger (more boiler plate).
Anyways. This post is not about explaining the process as much as it is about automating it.
Enter: Trigger maker
Heres how it works:
(sample RFC table ddl is provided in the download)
- Put a file "
.txt" in the input folder containing your table DDL (generated from SSMS option to script to a new window)
- Put the file "
.k" in the input folder continaing your keys seperated by newlines. Put the file ".i" containing columns you do not want to audit changes for. Put the file ".a" containing the audit code. e.g: Insert into CNAH values (@BSCNEW,@CELL_IDNEW,'%(param)s',@%(param)sOLD,@%(param)sNEW,null,null,@NCELL_IDNEW,null,null,GetDate())
- Run process.py
- Your trigger will be present in the output folder as "
Note trigger maker does one more cool thing for you. Here's how I like my auditing structure:
- Say you have table
- I like my trigger name to be