Logging parameter changes in a table using triggers in SQL Server similar to Oracle

Here is some code I wrote to make my oracle to SQL Server migration relatively painless.

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
begin
if :old.CBCHD<>:new.CBCHD then
insert into cna_hist
values
(:old.cell_id,'CBCHD',:old.CBCHD,:new.CBCHD,NULL,NULL,NULL,sysdate,NULL,:OLD.BSC);
end if;
end

Notice:

• 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:

1. In SQL server trigger is called ONCE will all the old values in deleted and new values in inserted .
2. No option like 'for each row'

So in case you want to loop through variable the same as oracle you need to write a LOT of boiler plate (to my knowledge..... I would LOVE to be proven wrong)

Heres a short description of how to do this:

1. Declare column members as variables.
2. Declare a cursor for select * from inserted inner join deleted on ()
3. loop through cursor pushing the values into your declared variables.
4. 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.

1. DECLARE
   @RFCIDOLD int,
   @RFCIDNEW int,
   @ProjectOLD varchar(255),
   @ProjectNEW varchar(255) ........etc
   
2. DECLARE Item CURSOR FOR
   
   select
   deleted.RFCID as RFCIDOLD,
   inserted.RFCID as RFCIDNEW,
   deleted.Project as ProjectOLD,
   inserted.Project as ProjectNEW ...... etc
   from
   deleted inner join inserted on
   (
   inserted.RFCID=deleted.RFCID
   )
   
3. Open Item
   fetch Item into
   @RFCIDOLD ,
   @RFCIDNEW ,
   @ProjectOLD ,
   @ProjectNEW .....etc
4. While @@Fetch_Status = 0
   begin
   --You now have the required data in the new and old values same as oracle. Do as you like.
   if @RequestedByOLD <> @RequestedByNEW
   begin
   Print @RequestedByOLD +' Changed to ' +@RequestedByNEW
   end
   
   fetch Item into
   @RFCIDOLD ,
   @RFCIDNEW ,
   @ProjectOLD ,
   @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 ".txt>"

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 (tablename)AUDIT

Enjoy!