Re: why is this trigger not working

> > CREATE OR REPLACE TRIGGER UpdateInstance
> > FOR UPDATE ON Survey_Instance
> > AS
> >
> > DECLARE sp_Survey_instance_id int;
> > sp_Survey_instance_id = SELECT Survey_instance_id FROM inserted;
> > UPDATE Survey_Instance SET Survey_Instance.Update_datetime =
> > SYSDATE WHERE Survey_Instance.Survey_instance_id =
> > sp_Survey_instance_id;
> > END;
>

This trigger wil not be compiled. The code under will work and have the same logic as in yors. How ever I think the trigger below that is wat you should use.

CREATE OR REPLACE TRIGGER UpdateInstance BEFORE UPDATE ON Survey_Instance
AS

DECLARE
  sp_Survey_instance_id NUMBER; -- Use INTEGER or NUMBER BEGIN -- You Must use BEGIN

• I hope this gives only one row SELECT Survey_instance_id INTO sp_Survey_instance_id -- In PL/SQL you have to select into somthing FROM inserted;
• No need to prefix table name here, you only use one table UPDATE Survey_Instance SET Update_datetime = SYSDATE WHERE Survey_instance_id = sp_Survey_instance_id;

END;
/

CREATE OR REPLACE TRIGGER UpdateInstance BEFORE UPDATE ON Survey_Instance
FOR EACH ROW
AS

BEGIN -- You Must use BEGIN

• Use the :new bind varible :NEW.Update_datetime := SYSDATE;

END;
/ Received on Sat Sep 21 2002 - 03:01:28 CEST