Oracle 5.0, Reference Manual

Page: 1069 / 3232

CREATE TRIGGER
Syntax
1049
For more information about trigger security, see Section 18.5, “Access Control for Stored Programs
and Views” .
Within a trigger, the
CURRENT_USER()
[959] function returns the account used to check privileges at
trigger activation time. Consistent with the privilege-checking rules just given,
CURRENT_USER()
[959]
returns the
DEFINER
user from MySQL 5.0.17 on. Before 5.0.17,
CURRENT_USER()
[959] returns
the user whose actions caused the trigger to be activated. For information about user auditing within
triggers, see
Section 6.3.8, “SQL-Based MySQL Account Activity Auditing” .
If you use
LOCK TABLES
to lock a table that has triggers, the tables used within the trigger are also
locked, as described in Section 13.3.5.2, “
LOCK TABLES
and Triggers” .
Note
Before MySQL 5.0.10, triggers cannot contain direct references to tables by
name. Beginning with MySQL 5.0.10, you can write triggers such as the one
named
testref
shown in this example:
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Suppose that you insert the following values into table
test1
as shown here:
mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
As a result, the data in the four tables will be as follows:
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+