Trigger not working
I have two databases. One is the db1 and the other is the db2.
When now a new row has been inserted on db1, a new row should be added in
the db2. But to get some neccessary data for inserting in the db2 we have to
make some additional selects in the db1 to get the hostname. So we have to rush
over several tables in order to get the proper hostname. In my trigger below the
Hostname is always empty when adding a row to db 1. In the command line it says:
Error in query [INSERT INTO triggers (triggerid,description,type,priority,status,comments,url,value,error,templateid) values (13029,'myDescription Text',1,1,0,'','',2,'Trigger just added. No status update so far.',0)] [Column 'Hostname' cannot be null]
Here my Trigger:
CREATE TRIGGER INSERT_Heartbeat AFTER INSERT ON db1.triggers
FOR EACH ROW BEGIN
SET @newId = new.triggerid;
SET @newDesc = new.description;
SELECT itemid FROM functions where triggerid=@newId INTO @itemId;
SELECT hostid FROM items where itemid = @itemId INTO @hostId;
SELECT host from hosts where hostid= @hostId INTO @hostName;
INSERT INTO db2.Hearbeat_tbl (ID,EventName,Hostname,UpdInterval,State,ExternalCmd,ThreadID)
VALUES (new.triggerid,new.description,hostName,'10',0,'MyExternalCommand',0);
END;