Re: Simple Trigger Question

In our last gripping episode "Abey Joseph" <abeyjoseph_at_hotmail.com> wrote:
> 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;
>
>

Why do you believe that the trigger is not working? I have installed your trigger on my 7.3.4 database, built both a main_table and a dest_table (albeit probably not quite the same as yours) and the trigger works without difficulty. Are you expecting to see the new column date in your dest_table? You will not, since you are updating the column with SYSDATE. I have cleared the dest_table several times, updated the main_table numerous times and have found that the trigger populates the dest_table properly, for every update of the column_date field. When the column_date field is populated in dest_table I find it difficult to know that the trigger actually performed the correct actions, however when I have set the column_date field to null for all records in dest_table and proceeded to update main_table the column_date field was properly populated with the current system date and time.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.