Create MySQL, SQL Server, and Oracle triggers

Scenario: when the users table is in insert, update, delete, the changed id is recorded in the users_log table.

Create a MySQL trigger

Trigger Time: BEFORE, AFTER

Trigger Event: INSERT, UPDATE, DELETE

NEW and OLD Keywords:

| Action | NEW | OLD |

| -------- | ---------------- |

| Insert new data | none |

| Update | modified to new data | modified original data |

| Delete | none | the original data to be deleted |

DELIMITER $
Create trigger insert_user AFTER INSERT
ON users FOR EACH ROW
BEGIN
Insert into users_log (user_id, action) VALUES (NEW. id, 'insert ');
END $
DELIMITER;

SQL Server trigger creation

Trigger Time: instead of (before), FOR (after)

Trigger Event: INSERT, UPDATE, DELETE

INSERTED and DELETED logical (concept) tables: act as the NEW and OLD keywords in MySQL. However, MySQL represents a record, while SQL Server represents a temporary table of all records.

Create trigger insert_user ON users FOR INSERT
BEGIN
DECLARE @ id INT
SELECT @ id = MIN (id) from inserted while @ id IS NOT NULL
BEGIN
Insert into users_log (user_id, action) VALUES (@ id, 'insert ')
SELECT @ id = MIN (id) from inserted where id> @ id
END
END

Create an Oracle trigger

Trigger Time: BEFORE, AFTER

Trigger Event: INSERT, UPDATE, DELETE

: NEW and: OLD Keywords: the function is equivalent to the NEW and OLD keywords in MySQL.

Create or replace trigger insert_user AFTER INSERT
On scott. "users" FOR EACH ROW
BEGIN
Insert into SCOTT. "users_log" ("user_id", "action") VALUES (: NEW. "id", 'insert ');
END;
/