CODEWITHSUNDEEP
sqloracle-databasetriggers
Shmuli
Shmuli

Reputation: 155

SQL trigger error - invalid trigger

I'm using pl\sql developer and I have a report table with a number(38) ID column.

I want to keep track of all updates for this table so I created another table like this:

 CREATE TABLE reportUpdate (report_id number(38), updatedate number(32));

And I created a trigger:

CREATE or REPLACE TRIGGER BeforeUpdateReport 
BEFORE 
UPDATE ON REPORT 
FOR EACH ROW 
Begin 
   INSERT INTO reportUpdate 
   Values(old.ID,sysdate); 
END;

And when I run it, I get an error, saying: trigger 'SYSTEM.BEFOREUPDATEREPORT' is invalidand failed re-validation.

Can someone please help

Upvotes: 0

Views: 1438

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use show errors after you see compiled with warnings, or query the user_errors view to see what is wrong later.

One obvious thing is that you haven't prefixed the old reference with a colon:

CREATE or REPLACE TRIGGER BeforeUpdateReport 
BEFORE 
UPDATE ON REPORT 
FOR EACH ROW 
Begin 
   INSERT INTO reportUpdate 
   Values(:old.ID,sysdate); 
END;
/

It's also better to specify the target table fields in the insert statement:

   INSERT INTO reportUpdate (report_id, updatedate)
   Values(:old.ID,sysdate);

But you have update_date defined in your table creation script as number(32), which doesn't make sense. As @realspirituals pointed out, it should be:

CREATE TABLE reportUpdate (report_id number, updatedate date);

Upvotes: 1

Related Questions