Menu
I've been hearing about triggers, and I have a few questions.
What are triggers?
How do I set them up?
Are there any precautions, aside from typical SQL stuff, that should be taken?
992
To create a trigger or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statement, described in Section 13.1. 22, “CREATE TRIGGER Statement”, and Section 13.1. 34, “DROP TRIGGER Statement”. Here is a simple example that associates a trigger with a table, to activate for INSERT operations.
Create Multiple Triggers Use the BEGIN and END delimiters to indicate the trigger body: CREATE TRIGGER <trigger name> <trigger time > <trigger event> ON <table name> FOR EACH ROW BEGIN <trigger body>; END; Make sure to change the default delimiter before creating a trigger with multiple operations.
Let's see the steps to create a BEFORE INSERT Trigger using MySQL Workbench. #1) Right-Click on the table name where the trigger needs to be created. #4) Click on the '+' sign adjacent to the BEFORE INSERT section to create a trigger. #6) Click “Apply” to Save the Trigger.
Triggers allow you to perform a function in the database as certain events happen (eg, an insert into a table).
I can't comment on mysql specifically.
Precaution: Triggers can be very alluring, when you first start using them they seem like a magic bullet to all kinds of problems. But, they make "magic" stuff happen, if you don't know the database inside out, it can seem like really strange things happen (such as inserts into other tables, input data changing, etc). Before implementing things as a trigger I'd seriously consider instead enforcing the use of an API around the schema (preferably in the database, but outside if you can't).
Some things I'd still use triggers for
Things you wouldn't want to use triggers for
170
From dev.mysql.com, a trigger is
...a named database object that is associated with a table and that is activated when a particular event occurs for the table.
The syntax to create them is also documented at that site.
Briefly,
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
And they provide an example:
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
You at least need to abide by all the restrictions on stored functions. You won't be able to lock tables, alter views, or modify the table that triggered the trigger. Also triggers may cause replication problems.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
