[SOLVED] Trigger Syntax

[josborne]

I think I just need a new set of eyes on this.

 

I have never used triggers before but need one now. From what I can tell, I have the syntax right and the trigger should work but since it is giving me a syntax error when I try to create it, I am obviously wrong.

 

CREATE TRIGGER Val_Update_TR AFTER INSERT ON Unit_tbl
FOR EACH ROW
BEGIN
UPDATE Pred_tbl SET Val_Against = (

SELECT SUM( Unit_For ) / SUM( Unit_Against )
FROM Unit_tbl
WHERE Pred_ID = NEW.Pred_ID
)
WHERE Pred_ID = NEW.Pred_ID;
END;

 

The SELECT runs fine on its own (SELECT SUM( Unit_For ) / SUM(Unit_Against ) FROM Unit_tbl WHERE Pred_ID = NEW.Pred_ID) so it must be with the trigger code.

 

Help?

[gassaz]

Try running the update + select without the trigger..

 

 

[josborne]

I actually pulled the update from a query I run regularly. I was trying to build the trigger to remove the manual labor.

 

So yes, the query works on its own but the trigger isn't being accepted.

[gassaz]

Maybe if you store the select into a variable and use the variable to the update query..

 

Something like this..

 

SET @resultset = (SELECT SUM( Unit_For ) / SUM( Unit_Against )
FROM Unit_tbl
WHERE Pred_ID = NEW.Pred_ID);

UPDATE Pred_tbl SET Val_Against = @resultset WHERE Pred_ID = NEW.Pred_ID;

[josborne]

Maybe I am confused about what you are getting at but I tried:

 

CREATE TRIGGER Val_Update_TR AFTER INSERT ON Unit_tbl
FOR EACH ROW
BEGIN
SET @resultset = (SELECT SUM( Unit_For ) / SUM( Unit_Against )
FROM Unit_tbl
WHERE Pred_ID = NEW.Pred_ID);

UPDATE Pred_tbl SET Val_Against = @resultset WHERE Pred_ID = NEW.Pred_ID;

 

This produces a syntax error as well.

[Mchl]

Did you change delimiter before defining a trigger?

[josborne]

I dod not originall change the delimiter. I just tried it changing the delimiter to | but that didn't help.

 

I have looked at this thing a hundred times and I cannot figure out what it wrong. I have compared it to countless examples of triggers I have come across and can't find the problem.

[gassaz]

Try  this..

 

DELIMITER $$

USE `db`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tai_1`$$

CREATE
    TRIGGER `ta1_1` AFTER INSERT ON `tablename` 
    FOR EACH ROW BEGIN
        SET @resultset = (SELECT SUM( Unit_For ) / SUM( Unit_Against )
        FROM Unit_tbl
        WHERE Pred_ID = NEW.Pred_ID);

        UPDATE Pred_tbl SET Val_Against = @resultset WHERE Pred_ID = NEW.Pred_ID;
    END;
$$

DELIMITER ;

[josborne]

gassaz, I believe that worked. No syntax error but now it appears I have a permissions issue (must be Super to create triggers). I am tracking down why I don't have permissions now. Hopefully, when I get that fixed I will be on my way.

 

I'll update as soon as I know what is going on.