Trigger question for columns with text in MS sql server 2000

• rob tay

  Newbie

  Points: 7

  More actions

  November 12, 2006 at 8:15 am

  #95007

  Trigger question

  All,

   

  I am adding audit trail to the database using sql server 2000.

  I am being forced to use Instead of Insert trigger for tables that have text columns as sql server 2000 does not support for/after triggers for text,ntext and  image columns in the table.

   

  I want to insert the new row on the same table and then update the audit table with those values. It is just updating the audit table and the column containing the base table primary key is having the value 0. Also, It does not insert the row into the base table.

   

  But it errors out with the message "Instead of trigger does not support direct recursion".

   

  Example -- psuedocode

  Table A (column1 int Identity(1,1) (primary key with identity), column2 varchar(10), column3 text)

  Table Aaudit (to store the audit trail)

  Create trigger trinsTable

  instead of insert on Table A

  as

  begin transaction

  Select into #inserted from inserted

  Exec (Alter table #inserted add auditdate datetime, add audituser varchar(10))

  Exec (insert TableA (column 2, column 3) Select column 2, column 3 from #inserted)

  Exec (insert Table Aaudit select * from inserted)

  .....

  end

  What is the workaround?

   

  Also, I am considering only single row insert at the present time. There may be other tables that may have multiple row inserts. Do I have to use cursors or anything else for that?

   

  I also tried using the for/after trigger with JOIN on the base table with the text column and that did not work.

  I am running out of options and I am willing to try any suggestions!

  Thanks in advance for your time

  Rob

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 12, 2006 at 10:01 am

  #671246

  you need to try to make all of your triggers allow multi insert, it will only cause problems later if you don't

   

  Try

  Create trigger trinsTable

  instead of insert on Table A

  as

  Begin

  -- begin transaction -- No need for this Transactions are implicit with triggers

  -- Select into #inserted from inserted -- NO need for this Inserted lives for the entire time the trigger lives

  -- Exec (Alter table #inserted add auditdate datetime, add audituser varchar(10)) --Why are you doing this?

  -- WHy are you wrapping tsql inside exec statements? No need

  insert TableA (column 2, column 3)

  Select column 2, column 3 from inserted

  insert Table Aaudit (Column1, Column2, Column3, AuditDate)

  select Column1, Column2, Column3, Getdate()

  from inserted

  end

  GO