| Trigger and integrity constraint [message #38809] |
Thu, 16 May 2002 05:59  |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
My emp and dept tables were created like this.
CREATE TABLE EMP (
EMPNO NUMBER (4) NOT NULL,
ENAME VARCHAR2 (10),
DEPTNO NUMBER(2),
CONSTRAINT PK_EMP
PRIMARY KEY ( EMPNO ) );
CREATE TABLE DEPT (
DEPTNO NUMBER (2) NOT NULL,
DNAME VARCHAR2 (14),
CONSTRAINT PK_DEPT
PRIMARY KEY ( DEPTNO ) );
ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO
FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO) ;
Now i have inserted values in both the tables. Now If i change the deptno of a department say from 30 to 40 in the dept table, oracle throws an integrity constraint error.
My problem is how do i write a trigger so that any change to the deptno column in the dept table will get reflected in the emp table bypassing the integrity constraint problem?
Can u let me have the syntax?
|
|
|
|
| Re: Trigger and integrity constraint [message #38818 is a reply to message #38809] |
Thu, 16 May 2002 15:19  |
Phenoracle
Messages: 35
Registered: March 2001
|
Member |
|
|
Now this works with 8.0 and upwards. By deferring the integrity constraint validation until the transaction is complete you are able to update the primary key and then the foreign key.
SQL> create table t (a int)
2 /
Table created.
SQL> create table s (a int)
2 /
1* alter table t addd constraint pk_a primary key (a)
2 /
Table altered.
1 alter table s add constraint fk_a
2 foreign key (a)
3 references t (a)
4* initially deferred
5 /
SQL> insert into t values (1)
2 /
1 row created.
SQL> insert into s values (1)
2 /
1 row created.
SQL> update t set a= 2
2 /
1 row updated.
SQL> update s set a=2
2 /
1 row updated.
SQL> commit;
Commit complete.
Table altered.
I think you could use BEFORE UPDATE in the trigger as wan alternative.
Hope this is of help
Have Fun
Phenom
|
|
|
|
| Re: Trigger and integrity constraint [message #38822 is a reply to message #38818] |
Thu, 16 May 2002 18:12 |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
That's ok when u are about to create a table. But i alreaday have a table with lot of data and the foreign key constraints are already in place. Now i need to change the value of the deptno in the dept table and i want it to get reflected in the emp table as well. If u have a solution for this please get back to me.
It's urgent.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
