Re: Simple Trigger Question

I always put some dbms_output code in triggers and run some test insert/update/delete statements in sql*plus just to make sure they are firing correctly. Just issue: set serveroutput on prior to doing the dml and watch for the messages.

"Abey Joseph" <abeyjoseph_at_hotmail.com> wrote in message news:hmCM5.45$H25.514_at_client...
> Folks,
> I am trying to create a simple trigger (Oracle 7.3.4/AIX 4.3) on a
> table that should insert a few fields into a different table when a field
 in
> the main table is updated. The trigger compiles fine (I don't get an
 error
> message), but doesn't work. When I check the status it is enabled!
 Please
> see if someone can help me.
>
> Here's the trigger...
>
> CREATE OR REPLACE TRIGGER trigger_xyz
> AFTER UPDATE OF column_date ON main_table
> FOR EACH ROW
> DECLARE
> numrows NUMBER;
> BEGIN
> IF :NEW.column_date IS NOT NULL
> THEN
> SELECT COUNT(*) INTO numrows
> FROM dest_table
> WHERE column_key = :NEW.column_key;
>
> IF (numrows > 0)
> THEN
> UPDATE dest_table
> SET column_date = SYSDATE,
> column_flag = 'N'
> WHERE column_key = :NEW.column_key;
> ELSE
> INSERT INTO dest_table
> (column_key, column_date, column_flag)
> VALUES (:NEW.column_key, SYSDATE, 'N');
> END IF;
> END IF;
> END;
>
>
Received on Sun Nov 05 2000 - 17:43:51 CST