help~~problem of Trigger [message #285107] |
Mon, 03 December 2007 08:58  |
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   |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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  |
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!!
|
|
|