Thread: Trigger Procedures

Is there a way of passing NEW. fields into a trigger procedure - I know
that they are automatically available - but there is a reason...

We 'build' the trigger procedures when we define the table - typically
we have a common 'trace' procedure that gets fired on update/insert.
This function takes  a series of arguments - one of which is a variably
named foreign key.
ie the trace_action could be called as:
CREATE TRIGGER insert_sourcedetprodTrace BEFORE INSERT ON sourcedetprod
 FOR EACH ROW
    EXECUTE PROCEDURE trace_actions    (
  'insert',
  'sourcedet',
  'sourcedetprod',
  'Products Added',
    NEW.sourcedetRecNo,
    NEW.recNo
       );
OR it could be called as:
CREATE TRIGGER insert_sourcedetupgrTrace BEFORE INSERT ON sourcedetupgr
 FOR EACH ROW
    EXECUTE PROCEDURE trace_actions    (
  'insert',
  'sourcedetprod',
  'sourcedetupgr',
  'Upgrade Items Added',
    NEW.sourcedetprodRecNo,
    NEW.recNo
       );


At the moment, I can't seem to pass the NEW values into trace_actions.
Similarly, as trace_actions is a common process, I have no way of
knowing whether NEW contains 'sourceDetRecNo' or 'sourceDetProdRecNo'
(unless I case the table name)

I know I can get around this by splitting the common trace_actions into
table specific traces, but I'm hoping that there's something that I've
missed and I can use some form of syntax like above.......

--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772

Steve,

> At the moment, I can't seem to pass the NEW values into trace_actions.
> Similarly, as trace_actions is a common process, I have no way of
> knowing whether NEW contains 'sourceDetRecNo' or 'sourceDetProdRecNo'
> (unless I case the table name)

You don't pass NEW and OLD as parameters.  They are automatically available
inside the trigger procedure.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco