MYSQL triggers

[classic]

I have been playing with MYSQL triggers and the following allows you to populate another table with data from an existing table on an insert, update or delete.

In navicat goto design mode.
There is tab for triggers.
Name the trigger
Set Fires on to Before or After
Then select insert, update or delete


Trigger for update example

IF OLD.defence != NEW.defence
THEN
INSERT INTO triggers1
(defence,EquipmentID)
VALUES
(
NEW.defence,OLD.EquipmentID
);
END IF
('OLD.' is the previous value of the field defence . 'New.' is the new value of the field defence )

so when the defence value is changed I get the new value of defence & the
value of EquipmentID posted into the triggers1 table.

trigger for insert example


INSERT INTO triggers1
(imagetype,EquipmentID)
VALUES
(
NEW.EquipmentStateID,NEW.EquipmentID
)

I get the new values of EquipmentStateID & EquipmentID inserted into the triggers1 table.


I have some large tables that are updated with certain values. When they are updated we have to run some code to send out an SMS message. So rather than polling the large tables I poll the triggers1 table process the action then remove the record. So the triggers1 table remains fairly small.

Hope this is of help to you ans all.


Cheers

SteveW

[classic]

Another good resource is MySQL Reference Manual for version 5.0.3-alpha - 20 Triggers