Home » SQL & PL/SQL » SQL & PL/SQL » help~~problem of Trigger
help~~problem of Trigger [message #285107] Mon, 03 December 2007 08:58 Go to next message
side
Messages: 2
Registered: December 2007
Junior Member
I created two tables:
departments(dept_id char(8) primary key);
employee(emp_id char(10) primary key,department_id char(8),foreign key(department_id) references departments(dept_id));


There are just three values in departments,they are '001','002','003'

And I update the employee:

SQL>update employee set department_id='999' where emp_id='01';


Obviously,the SQL statement disobeyed Referential Integrity Rules
And it'll be refused by DBMS.
but there is a trigger be defined on table employee:
create trigger up_emp after update on employee for each row
begin
insert into departments values('999');
end;
/

The update operating completely successfully!!
It means that the update operating completed after
activate of trigger,but the trigger was defined as a after
type one!!
why?
thanks!

[EDITED by LF: added [code] tags (next time, please, do it yourself)]

[Updated on: Mon, 03 December 2007 15:17] by Moderator

Report message to a moderator

Re: help~~problem of Trigger [message #285109 is a reply to message #285107] Mon, 03 December 2007 09:32 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Prove that '999' did not exist before you performed the update statement.

DO a select before you do an update, and then after.
Re: help~~problem of Trigger [message #285118 is a reply to message #285107] Mon, 03 December 2007 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table dept (deptno number primary key);

Table created.

SQL> create table emp (
  2    empno number primary key,
  3    deptno number references dept);

Table created.

SQL> insert into dept select deptno from scott.dept;

4 rows created.

SQL> insert into emp select empno, deptno from scott.emp;

14 rows created.

SQL> create trigger emp_trg after insert on emp
  2  for each row
  3  begin
  4    insert into dept values (50);
  5  end;
  6  /

Trigger created.

SQL> select deptno from dept order by 1;
    DEPTNO
----------
        10
        20
        30
        40

4 rows selected.

SQL> insert into emp values (0,50);

1 row created.

SQL> select deptno from dept order by 1;
    DEPTNO
----------
        10
        20
        30
        40
        50

5 rows selected.

SQL> select * from emp where empno = 0;
     EMPNO     DEPTNO
---------- ----------
         0         50

1 row selected.

Statement INCLUDING all its triggers is a whole, constraints (foreign keys, primary key) are checked AFTER the execution of the whole statement.
Fortunately as this is because of this behaviour that you can execute statements on multiple rows like:
SQL> update dept set deptno = decode(deptno,10,20,20,10) where deptno in (10,20);

2 rows updated.

During the execution the primary key is violated.

Regards
Michel

[Updated on: Mon, 03 December 2007 10:59]

Report message to a moderator

Re: help~~problem of Trigger [message #287049 is a reply to message #285118] Tue, 11 December 2007 00:31 Go to previous message
side
Messages: 2
Registered: December 2007
Junior Member
Michel Cadot wrote on Tue, 04 December 2007 00:44

SQL> create table dept (deptno number primary key);

Table created.

SQL> create table emp (
  2    empno number primary key,
  3    deptno number references dept);

Table created.

SQL> insert into dept select deptno from scott.dept;

4 rows created.

SQL> insert into emp select empno, deptno from scott.emp;

14 rows created.

SQL> create trigger emp_trg after insert on emp
  2  for each row
  3  begin
  4    insert into dept values (50);
  5  end;
  6  /

Trigger created.

SQL> select deptno from dept order by 1;
    DEPTNO
----------
        10
        20
        30
        40

4 rows selected.

SQL> insert into emp values (0,50);

1 row created.

SQL> select deptno from dept order by 1;
    DEPTNO
----------
        10
        20
        30
        40
        50

5 rows selected.

SQL> select * from emp where empno = 0;
     EMPNO     DEPTNO
---------- ----------
         0         50

1 row selected.

Statement INCLUDING all its triggers is a whole, constraints (foreign keys, primary key) are checked AFTER the execution of the whole statement.
Fortunately as this is because of this behaviour that you can execute statements on multiple rows like:
SQL> update dept set deptno = decode(deptno,10,20,20,10) where deptno in (10,20);

2 rows updated.

During the execution the primary key is violated.

Regards
Michel



Thank you very much!!

Previous Topic: trying to update table 1 but int he where clauses it is joining two tables
Next Topic: How to search a string in a text file
Goto Forum:
  


Current Time: Mon Dec 05 12:41:26 CST 2016

Total time taken to generate the page: 0.11588 seconds