You do not need a RETURN 1
because Trigger is a Stored Procedure, not a Stored Function.
If you want to break it on purpose, that's acceptable.
I wrote two posts about how to break a trigger midstream
Try this:
CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply`
FOR EACH ROW
BEGIN
DECLARE dummy INT;
IF NEW.cname = "hi" THEN
SET NEW.cname = "hello";
ELSE
SELECT no_such_column INTO dummy
FROM information_schema.no_such_table;
END IF;
END; $$
1.
I notice in your trigger, your code says:
INSERT INTO LYNX.TRANS_AUDIT
(TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
VALUES
(TA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);
But shouldn't the VALUES clause contain vTA_ID
instead?
INSERT INTO LYNX.TRANS_AUDIT
(TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
VALUES
(vTA_ID, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);
However, calculating vTA_ID with your query, may not be your most efficient option.
2.
You could create a SEQUENCE object, and let it always calculate new values.
CREATE SEQUENCE TRANS_AUDIT_SEQ AS INTEGER;
Then in your trigger,
INSERT INTO LYNX.TRANS_AUDIT
(TA_ID, TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
VALUES
(NEXT VALUE FOR TRANS_AUDIT_SEQ, N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);
3.
But what may suit you best, may be to define your ID column AS IDENTITY
CREATE TABLE LYNX.TRANS_AUDIT (
TA_ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
...
PRIMARY KEY (TA_ID)
);
Then in your trigger, you do not need to specify the value, since it will be generated for you:
INSERT INTO LYNX.TRANS_AUDIT
(TA_KEY_VALUE, TA_TABLE_CHANGED, TA_FIELD_CHANGED, ...)
VALUES
(N.UNIT_ID , 'PUNIT', 'FLEET_ID', ...);
This is most likely the best of the 3 solutions for you, unless there are other factors not mentioned.
Best Answer
Use a
BEFORE
trigger instead, and set theupdated
column assigning a value toNEW.updated
(this would be a simple assignment, not anUPDATE
). This way you won't trigger additionalUPDATE
s.Your trigger body will simply look like
I usually use
AFTER
triggers only for modifying other tables,BEFORE
for modifying the new (or updated) row, or suppressDELETE
.