SQL trigger error - invalid trigger

Question

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

You most probably got a trigger created with warning when you created the trigger. Do a show errors and you'll see what is wrong with the code — user330315
– user330315, Commented May 9, 2014 at 9:33
@Shmuli: Before the edit, you had "(Oracle)" in the title. The consensus on StackOverflow is, that tags shouldn't go in the title, but in the tags field. — waka
– waka, Commented May 9, 2014 at 9:46
You might need to change CREATE TABLE reportUpdate (report_id number(38), updatedate date); But not so sure. — Srini V
– Srini V, Commented May 9, 2014 at 9:57

Alex Poole · Accepted Answer · 2014-05-09 10:00:25Z

1

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);

answered May 9, 2014 at 10:00

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL trigger error - invalid trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related