Well,

If you are going to write the update and delete trigger for each table that passes the PK and action, you might as well just update the changelog from the trigger.

You can do what you are doing, you just have to basically put the whole sp into the string and execute it at once...it is like mental gymnastics. Really, once you write all thre triggers you want have to touch tme much. Also, you could write an SP to write the triggers.


>I'm trying to create a trigger which logs the primary key of records changes.
>Here is my code for the stored procedure
>

>create procedure WriteLog
>  @Table			varchar(30) = " ",
>  @PKfield			varchar(20) = " ",
>  @action			char(1) = " "
>as
>  declare @PKvalue varchar(50), @GetKeyCommand char(50)
>  -- Get key value to insert into table
>  if @action = "D"
>    set @GetKeyCommand = "@PKvalue = (select " + @PKfield + " from deleted)"
>  else
>    set @GetKeyCommand = "@PKvalue = (select " + @PKfield + " from inserted)"
>  exec (@GetKeyCommand)
>  -- Write the record into ChangeLog table
>  insert into ChangeLog (CL_TABLE, CL_PKFIELD, CL_PKVALUE, CL_ACTION,
>     CL_DATETM) values (@Table, @PKfield, @PKvalue, @Action, getdate())
>

>
>I call the procedure with:
>

>create trigger APLANA on APLAN for insert as
>  begin
>    declare @WriteLog char(1)
>    execute WriteLog "MYTABLE", "MY_PRIMKEY", "A"
>  end
>

>
>When I try to add a record I get the error message (something like) "Need to declare variable @PKvalue." In addition, it doesn't want to recognize the dummy tables DELETED and INSERTED.
>
>Is this the wrong way to do this? Isn't there a way to get this information inside a subroutine without having to code identical code for all tables -- which creates a maintenance nightmare.
>
>Thanks for your help, anyone.