Tuesday, July 1, 2014

Example, create and use triggers in MySQL



We can create a trigger user_trig in MySQL
CREATE TRIGGER user_trig BEFORE INSERT
        ON users FOR EACH ROW
        INSERT INTO trigger_time VALUES(NOW())

When we execute an insert statement on users table, the trigger user_trig is executed and current time is inserted.
Tables users and trigger_time are created as follows:
CREATE TABLE users
(
user_id varchar(45) NOT NULL,
salary int NOT NULL DEFAULT 0,
PRIMARY KEY (user_id)
)

CREATE TABLE trigger_time
(
exec_time datetime NOT NULL
)

When we
INSERT INTO users VALUES('jiansen',6000)
In table trigger_time, we can see
2014-07-01 21:57:25
To see the triggers we created
SHOW TRIGGERS;
which is the same as
SELECT * FROM information_schema.triggers;
The trigger information is stored in triggers table in database information_schema
To delete trigger user_trig
DROP TRIGGER user_trig;
 Video: Example, create and use triggers in MySQL

No comments:

Post a Comment