Re: Trigger Woes

As I already expected, you are performing an unnecessary select it should be either

 CREATE OR REPLACE test_trigger AFTER
 INSERT OR UPDATE OF seconds ON test
 FOR EACH ROW  DECLARE
 new_date DATE;

 BEGIN  SELECT TO_DATE('01-01-1970 00:00:00', 'mm-dd-yyyy hh24:mi:ss') +  (1/24/60/60)*TO_NUMBER(:new.seconds)
 INTO new_date
 FROM dual;

 :new.dates := new_date;

 END;
 /

or better still

 CREATE OR REPLACE test_trigger AFTER
 INSERT OR UPDATE OF seconds ON test
 FOR EACH ROW  DECLARE
 new_date DATE;

 BEGIN  :new.dates := TO_DATE('01-01-1970 00:00:00', 'mm-dd-yyyy hh24:mi:ss') + (1/24/60/60)*TO_NUMBER(:new.seconds);
 END;
 /

Hth,

Sybrand Bakker, Oracle DBA

<kal121_at_yahoo.com> schreef in berichtnieuws 8cvrb3$6r8$1_at_nnrp1.deja.com...
> I keep getting this error message whenever I try to compile my trigger:
>
> SP2-0552: Bind variable "NEW" not declared.
>
> This is the trigger definition:
>
> CREATE OR REPLACE test_trigger AFTER
> INSERT OR UPDATE OF seconds ON test
> FOR EACH ROW
>
> DECLARE
> new_date DATE;
>
> BEGIN
>
> SELECT TO_DATE('01-01-1970 00:00:00', 'mm-dd-yyyy hh24:mi:ss') +
> (1/24/60/60)*TO_NUMBER(:new.seconds)
> INTO new_date
> FROM test
> WHERE id = :new.id;
>
> :new.dates := new_date;
>
> END;
> /
>
> This is the table definition:
>
> SECONDS varchar2(10)
> DATES date
> ID integer
>
> Please respond to my email - deja.com is being flaky today
> Thanks!!!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Apr 11 2000 - 00:00:00 CDT