MySQL Forums
Forum List  »  Triggers

Triggers... They run well or not?
Posted by: Lucio Menci
Date: September 24, 2013 08:59AM

Hi,
I made some triggers to store in a dummy table, all changes to make them also in another schema.

The triggers records in the table the table source name, the operation (I/D/U), the list of key columns, if the transfer is made or not (0: to do, 1: done).

These are the triggers:

DELIMITER //
CREATE TRIGGER AnagI AFTER INSERT ON anag FOR EACH ROW
INSERT INTO ToTrasf (`TrEvent`, `OP`, `State`, `TrasfKey`) VALUES ('anag', 'I', 0, New.Code);//
CREATE TRIGGER AnagD BEFORE DELETE ON anag FOR EACH ROW
Insert into ToTrasf (`TrEvent`, `OP`, `State`, `TrasfKey`) VALUES ('anag', 'D', 0, Old.Code);//
CREATE TRIGGER AnagU AFTER UPDATE ON anag FOR EACH ROW
BEGIN
IF New.Code = Old.Code Then
INSERT INTO ToTrasf (`TrEvent`, `OP`, `State`, `TrasfKey`) VALUES ('anag', 'U', 0, New.Code);
ELSE
INSERT INTO ToTrasf (`TrEvent`, `OP`, `State`, `TrasfKey`) VALUES ('anag', 'D', 0, Old.Code);
INSERT INTO ToTrasf (`TrEvent`, `OP`, `State`, `TrasfKey`) VALUES ('anag', 'I', 0, New.Code);
END IF;
END;//

I transferred data from one scheme to another with a command like this, running on more tables:
Insert into schemaA.anag select schemaB.anag where not exists (select * from SchemaA.anag Where SchemaA.anag = SchemaB.Anag)

When I transferred the data, I saw that the table ToTrasf was filled only for some table.

I tried to create new data in the tables where the trigger did not work, and this time it worked fine.

It seems that it can not be expected if a trigger functions well or not.

I have MySQL 5.1.53 community edition via TCP/IP.

It's a old version? Can I be more lucky with a newer version? There may be compatibility issues?

Options: ReplyQuote


Subject
Views
Written By
Posted
Triggers... They run well or not?
1586
September 24, 2013 08:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.