Need Help with Database Trigger

[Gol]

Here is two tables: -

Table A cols
Message
location
city

Table B cols
Message
location
city

I want to create a trigger, so that when any matching message column "HELLO" comes to table A, it gets to table B and get deleted from table A.

[debasisdas]

please specify clearly

which record u want to insert to table B
(old or new)
which record u want to delete from table A
(old or new)

and do u want to matching a particular word or what

only then i can send u the code if u want

[wel]

try this ... hope that will help
we assume :
- Tables and trigger are in scott's schema
- Table A col Message
- Table B col Message
- The trigger is named TRIG_HELLO , will fire BEFORE INSERT on table A and when message is 'HELLO'

CREATE OR REPLACE TRIGGER "SCOTT".TRIG_HELLO
BEFORE INSERT ON "SCOTT"."A"
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (NEW.MESSAGE='HELLO')
BEGIN

delete from "SCOTT"."A" where message='HELLO';

insert into "SCOTT"."B" values ('HELLO');

commit;

END;

[debasisdas]

Dear wel

AS per the rule commands like COMMIT,ROLLBACK n SAVEPOINT can't be specified in a trigger body.

Please don't post such things in the forum which u r not sure yourself.