Trigger not firing [message #778] |
Mon, 04 March 2002 06:28 |
Todd
Messages: 17 Registered: October 2000
|
Junior Member |
|
|
I have created the following trigger without errors:
create or replace trigger projectstime
after update of tested on istesters
for each row
declare
time_now date;
begin
time_now := sysdate;
if updating ('tested = S') then
update istesters set dates = time_now;
end if;
if updating ('tested = E') then
update istesters set dates = time_now;
end if;
end;
When I attempt to update tested to 'S' I expect the column dates to be updated to sysdate. This never happens. Any ideas? I also noticed that the column tested does not exist in the dba_triggers table under the field column. Does this matter?
|
|
|
Re: Trigger not firing [message #781 is a reply to message #778] |
Mon, 04 March 2002 06:53 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
try this way, there are number of changes marked with ***
create or replace trigger projectstime
***before update of tested on istesters
for each row
declare
time_now date;
begin
time_now := sysdate;
***if updating ('tested') and :new.tested = 'S' then
***:new.dates := time_now;
end if;
***if updating ('tested') and :new.tested = 'E' then
***:new.dates := time_now;
end if;
end;
|
|
|
Re: Trigger not firing [message #787 is a reply to message #778] |
Mon, 04 March 2002 08:44 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Since you specified this as a 'for each row' trigger, don't you want to only update the dates value for the row that you updated (as opposed to all rows in the table)?
If so, this needs to be a 'before' trigger (instead of 'after') and you can set the values using :new. Also, since this is a 'before update' trigger, you don't need to check for 'updating' within the trigger.
create or replace trigger projectstime
before update of tested on istesters
for each row
begin
if :new.tested in ('S', 'E') then
:new.dates := sysdate;
end if;
end;
/
|
|
|