Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not firing
Trigger not firing [message #778] Mon, 04 March 2002 06:28 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;
/
Previous Topic: One JDBC question
Next Topic: multiple cursors
Goto Forum:
  


Current Time: Fri Mar 29 04:41:36 CDT 2024