| TRIGGER on data manual change [message #19716] |
Thu, 04 April 2002 12:21  |
bechir
Messages: 23
Registered: November 2001
|
Junior Member |
|
|
would any one tell me what's the problem in the attached trigger. I'm trying to update automatically the STATUS field to 'OLD' in TABLEX every time the user updates/changes the DESCRIPTION attribute of the same record.
Here is the trigger script:
/* ------------------------------------------------- */
CREATE OR REPLACE TRIGGER OWNER.TABLEX_AU_ROW
AFTER UPDATE OF DESCRIPTION ON OWNER.TABLEX
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (NEW.DESCRIPTION != OLD.DESCRIPTION)
BEGIN
UPDATE OWNER.TABLEX SET STATUS = 'OLD' WHERE TABLEX.DESCRIPTION = :NEW.DESCRIPTION;
END;
/
/* ------------------------------------------- */
|
|
|
|
|
|
| Re: TRIGGER on data manual change [message #19720 is a reply to message #19716] |
Thu, 04 April 2002 12:59  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
1) This needs to be a before update trigger (in order to change the status value).
2) To change the value of a column on the same row, you don't use an update statement - you just assign a value using :new.column_name.
3) Also, if you description column is nullable by any chance, your WHEN condition will have to take that into account (NVL).
create or replace trigger tablex_au_row
before update of description on tablex
for each row
when (new.description != old.description)
begin
:new.status := 'OLD';
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
