create a new trigger

[raf]

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

[nandu]

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

[raf]

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

[nandu]

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

[coderodeo]

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;
/