MySQL Forums
Forum List  »  Triggers

trigger not acting as expected
Posted by: Matteo Tassinari
Date: May 25, 2012 07:24AM

Hi everybody,

I have a problem with the following trigger:

CREATE TRIGGER `lin_97_25_log_insert` BEFORE INSERT ON lin_97_25_log FOR EACH ROW
BEGIN
IF NEW.id_user_sgs IS NULL OR NOT NEW.id_user_sgs THEN
SET NEW.id_user_sgs = 1;
SET NEW.nome_utente = 'SYSTEM';
ELSE
SET NEW.nome_utente = (
SELECT nome_utente FROM lin_97_2_1_user_sgs WHERE id_user_sgs = NEW.id_user_sgs AND id_azienda = NEW.id_azienda AND id_sede = NEW.id_sede AND revisione_documento = NEW.revisione_documento LIMIT 1
);
END IF;

IF NEW.nome_utente IS NULL OR NOT NEW.nome_utente THEN
SET NEW.id_user_sgs = 1;
SET NEW.nome_utente = 'SYSTEM';
END IF;
END $$

A little background: lin_97_25_log is a table which stores a log of all the modifications made to records in other tables of our database, among the others it has 2 fields, one to store the user id (id_user_sgs) and one to store the actual name of the user (nome_utente).

All the logging happens through other triggers in the database, which insert a log row without ever explicitly setting the name of the user, only the ID, and I need this trigger to recover the name.

The logic would be this:
if the user id is somehow not set or evaluates to false (eg. it is 0) then set the user id to 1 and the name to 'SYSTEM', else set the name to the corresponding name of the user from the user table, if the name is still not set or evaluates to false (eg. empty string) then set the user id to 1 and the name to 'SYSTEM'.

Where is the problem? I've noticed that ALL inserts in the log table happens with the user id set to 1 and name set to 'SYSTEM' even if the id_user_sgs is set and perfectly valid.

I've done some tests removing the last check, and it worked as expected.

What have I done wrong here?

Thanks for the assistance =)

Options: ReplyQuote


Subject
Views
Written By
Posted
trigger not acting as expected
1724
May 25, 2012 07:24AM


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.