Trigger Question:

[owenyuen]

Hello:
I try to create my first sample trigger regarding checks inserts and updates on the attribute LOC of the DEPT table. If the new value is 'Houston', change it to 'Chicago'.

***************************************************
CREATE OR REPLACE TRIGGER DEPT
BEFORE INSERT OR UPDATE
ON DEPT
BEGIN
If new.loc = 'Houston' Then
new.loc := 'Chicago';
End If;
END;
***************************************************
But I have a error as : Warning: Trigger created with compilation errors.

Anyone can help!!

Thanks

 

[sem]

You missed colons before "new" Regards, Dima

 

[owenyuen]

SQL> @ C:\oracle8\PLSQL\dept_tr
Hello Dima:
Thanks for your input. But after I added the colon in front of NEW, then another ERROR was coming up?

SQL> CREATE OR REPLACE TRIGGER DEPT_TR
2 AFTER INSERT OR UPDATE
3 ON DEPT
4 BEGIN
5 If :new.loc = 'Houston' Then
6 :new.loc := 'Chicago';
7 End If;
8 END;
9 /
CREATE OR REPLACE TRIGGER DEPT_TR
*
ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers

 

[swampBoogie]

You need a row trigger, not a statement trigger which is the default.

CREATE OR REPLACE TRIGGER DEPT
BEFORE INSERT OR UPDATE
ON DEPT
for each row
BEGIN

 

[sem]

Oops, it should be row-level trigger. Add FOR EACH ROW clause Regards, Dima

 

[owenyuen]

Thanks Dima:
NOW IS WORKING FINE NOW & REALLY THANKS FOR YOUR INPUT!!!

CREATE OR REPLACE TRIGGER DEPT
BEFORE INSERT OR UPDATE
ON DEPT
FOR EACH ROW
BEGIN
IF :NEW.LOC = 'Houston' THEN
:NEW.LOC := 'Chicago';
END IF;
END;