Printable View
Hi,
I've 2 tables (A, B)
A has this columns
cod varchar2(32) primary key
admin varchar2(32)
B has
cod varchar2(32)
cod_admin varchar2(32)
I'd to create a trigger that when I update A.admin with value 'X', in tab B I get B.cod=A.cod and B.cod_admin='999'.
This also for insert and delete.
Thanks
Raf
Raf Hi,
Can you please detail what should happen in Table B when you
do something in Table A?
I couldn't comprehend much with what u have posted.
Cheers
Nandu
ok
Tab B is empty
Tab A isn't empty
when I insert into col admin of A the values X I must have same value of col cod the tab A and the value '999' in col cod_admin of tab B.
It is important that when I delete the values in tab B it must delete also in tab A.
Raf
Hi
I am still not sure if I have understood your problem correctly.
Do you mean the following :
1. IF Insert values Cod = 'X', Admin = '123' in Table A, THEN
Insert values Cod = 'X', Cod_Admin = '999' in Table B
2. IF Delete Values in Table B, THEN
Delete values in Table A.
Is that all you mean?
1 a) Will the rows be deleted only in Table B and its effect be reflectred in Table A? Or even if it is deleted in Table A, it should be effected in Table B?
2 a) Similarly, will the inserts happen only in Table A? Or will it also be done in Table B and its effect should be reflected in Table A?
Case 1 : If only 1 & 2 are true, Create an insert trigger in Table A and ensure it inserts records in Table B. Create a Delete trigger in Table B and ensure it deletes records in Table A(Care should be taken if you create FOREIGN KEY relationships)
Case 2 : If all 1, 1 a), 2, 2 a) are true then create INSERT/DELETE trigger in both Table A and Table B.
HTH
Nandu
CREATE OR REPLACE TRIGGER trg_BIUDR_tableA
BEFORE INSERT OR UPDATE OR DELETE
ON a
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
NULL;-- write your code
ELSIF UPDATING('ADMIN') THEN -- that is only if you update field admin
IF :NEW.ADMIN = 'X' THEN
UPDATE b
SET cod = :NEW.cod,
cod_admin = '999'
WHERE cod_admin = :OLD.ADMIN;--????something like this?
END IF;
ELSIF DELETING THEN
NULL;--write your code
END IF;
END;
/