Web Development

Ask something
Question Locked

SQL Trigger using Informix database

0 helpful
11 replies

by huda

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I still new in SQL Trigger using Informix database. I really need help from the expert to solve my problem.

How can I TRIGGER tempTable on new record inserted then insert the new record into actualTable? After that I want to TRIGGER actualTable on new record inserted and delete the same record on tempTable?

Here is my SQL TRIGGER:

CREATE TRIGGER t1
INSERT ON tempTable
REFERENCING NEW AS original
FOR EACH ROW WHEN (original.status = 'Y')
(INSERT INTO actualTable
(field1,field2,field3,status)VALUES(original.field1,original.field2,original.field3,original.status));

CREATE TRIGGER t2
INSERT ON actualTable
REFERENCING NEW AS original
FOR EACH ROW WHEN (original.status = 'Y')
(DELETE tempTable WHERE field1=original.field1);

Here is error found:

Error: Table or column matches object referenced in triggering statement. (State:S1000, Native Code: FFFFFD15)

11 replies

by thumbsup2

Not sure when the SQL experts will be online. Keep bumping your post to keep it visible by replying to yourself until someone is able to help you.

by huda

Thanks for your tips & tricks

by huda

Can you please give me SQL experts ID or his/her e-mail address? So I can contact them directly.

It is better if they got YM ID or Gmail ID.

Thank you very much... for your support.

by thumbsup2

We don't email answers to anybody. They're posted here in a public forum for everyone to see.

You'll just have to wait until the SQL experts come online after the holidays.

by tony hopkinson

For those who were interested I put it down to attempting to delete the record that triggered the insert inside the a single transaction.

For those new to triggers, any SQL inside them is part of the transaction that triggers them. If the trigger fails for some reason the entire transaction that triggered it fails.

A decent DBMS will check it for circularity to avoid this trick
Insert into Table A triggers an insert into Table B.
Insert Into Table B triggers and insert into Table A

Not a desirable situation. :p

by huda

Thank you for reply Tony.

Merry Christmas and Happy New Year...

I really don't understand what you mean with this statement:

Insert into Table A triggers an insert into Table B.
Insert Into Table B triggers and insert into Table A

Is it you mean that I need to create one trigger (t1) for the occurance of insertion event in Table A. Then I need to create one trigger (t2) for the occurance of insertion event in Table B.

I have tried to insert Table B in t1 and delete Table A in t2 but fail.

Can you please give an example how to solve my problem? I just trying use trigger just a few days before.

Anyway thanks once again for your comment.

by tony hopkinson

You have to think of this chain of operations as one atomic operation, in your scenario you are trying to delete the record you are in the process of inserting.

The only way to do this is to break the chain.

I did something sorta of vaguely like this once, having been left with no way to design it out.
Essentially I just did the insert into temptable.
Then scheduled a stored procedure, that went and did the work. I could cope with the minute delay there though.

The otherway if it's acceptable is to write a stored procedure to do the work
So instead of inserting directly into temptable, call an sp with suitable parameters test the status and insert into temptable or actual as desired, no delete required then.

by huda

Thanks for your explanation and I really appreciated. Did you have any ym id or gmail id?

by huda

Merry Christmas and Happy New Year... ThymbsUp2.

I will to wait until the Expert help me :)

by tony hopkinson

I'm alwayss about on here, so a pm or a discussion / question will get me.

'cept on xmas day of course...

This discussion is closed to new replies.