Re: Antw: Re: Question and troubles with triggers (8i)

From: Arun Mathur <themathurs_at_gmail.com>
Date: 13 May 2005 07:18:18 -0700
Message-ID: <1115993898.290349.107020@o13g2000cwo.googlegroups.com>

Hi Peter,

I don't have an 8i instance to test against, but I did try it against my 9i development database. I don't use DBA studio that often, so I can't comment on how it generates a DDL script to a user. But, assuming it refers to the user_triggers view, I dont see how it could possibly conclude that the trigger should have an "update of <column names>" clause in the event that it wasn't written that way.

SQL> set echo on
SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for Solaris: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

SQL> drop table t;

Table dropped.

SQL> create table t(x int,y int,z int);

Table created.

SQL> create or replace trigger t_bur before update on t   2 for each row
  3 begin
  4 dbms_output.put_line('In the update row trigger.');   5 end;
  6 /

Trigger created.

SQL> select description from user_triggers   2 where trigger_name = 'T_BUR';

DESCRIPTION

---

t_bur before update on t

for each row

This is accurate, and when I replace this trigger to include the "update of" clause, the only fields that changed in the user_triggers view are the description and of course, the trigger_body. You're welcome to select all the columns from the user_triggers view. I just selected the description column for display purposes, but only after looking at all the columns first. At any rate, let's verify that the update trigger fires as expected.

SQL> insert into t(x,y,z) values (1,1,1);

1 row created.

SQL> set serveroutput on size 100000
SQL> update t set x=0;
In the update row trigger.

1 row updated.

SQL> update t set x=1;
In the update row trigger.

1 row updated.

SQL> update t set y=0;
In the update row trigger.

1 row updated.

SQL> update t set z=0;
In the update row trigger.

1 row updated.

Works as expected. Now, let's see what happens when I modify it to include the update of clause:

SQL> create or replace trigger t_bur before update of x on t for each row
  2 begin
  3 dbms_output.put_line('In the update row trigger because t updated.');
  4 end;
  5 /

Trigger created.

SQL> update t set x=0;
In the update row trigger because t updated.

1 row updated.

SQL> update t set y=0;

1 row updated.

SQL> update t set z=0;

1 row updated.

Works as expected. It only fired when I modified x (apologies for the dbms_output message. I meant to write 'In the update row trigger because X updated', but I think you get the idea). Now,let's see what the user_triggers view reports about this trigger:

SQL> select description from user_triggers   2 where trigger_name = 'T_BUR';

DESCRIPTION

---

t_bur before update of x on t for each row

Try the same actions on your environment, and see if you get the same behavior. From my session, both types of triggers fire when expected, so you may want to revisit the statements that you expected the triggers to execute. Good luck.

Regards,
Arun Received on Fri May 13 2005 - 09:18:18 CDT