1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
-- TRIGGER
CREATE TABLE test_tbl (i integer, s text);
ERROR: relation "test_tbl" already exists
CREATE FUNCTION test_trigger() RETURNS trigger AS
$$
pljs.elog(NOTICE, "NEW = ", JSON.stringify(NEW));
pljs.elog(NOTICE, "OLD = ", JSON.stringify(OLD));
pljs.elog(NOTICE, "TG_OP = ", TG_OP);
pljs.elog(NOTICE, "TG_ARGV = ", TG_ARGV);
if (TG_OP == "UPDATE") {
NEW.i = 102;
return NEW;
}
return NEW;
$$
LANGUAGE pljs;
CREATE TRIGGER test_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON test_tbl FOR EACH ROW
EXECUTE PROCEDURE test_trigger();
INSERT INTO test_tbl VALUES(100, 'ABC');
NOTICE: NEW = {"i":100,"s":"ABC"}
NOTICE: OLD = undefined
NOTICE: TG_OP = INSERT
NOTICE: TG_ARGV =
UPDATE test_tbl SET i = 101, s = 'DEF' WHERE i = 1;
NOTICE: NEW = {"i":101,"s":"DEF"}
NOTICE: OLD = {"i":1,"s":"s1"}
NOTICE: TG_OP = UPDATE
NOTICE: TG_ARGV =
DELETE FROM test_tbl WHERE i >= 100;
NOTICE: NEW = undefined
NOTICE: OLD = {"i":100,"s":"ABC"}
NOTICE: TG_OP = DELETE
NOTICE: TG_ARGV =
NOTICE: NEW = undefined
NOTICE: OLD = {"i":102,"s":"DEF"}
NOTICE: TG_OP = DELETE
NOTICE: TG_ARGV =
SELECT * FROM test_tbl;
i | s
---+----
2 | s2
3 | s3
4 | s4
(3 rows)
-- One more trigger
CREATE FUNCTION test_trigger2() RETURNS trigger AS
$$
var tuple;
switch (TG_OP) {
case "INSERT":
tuple = NEW;
break;
case "UPDATE":
tuple = OLD;
break;
case "DELETE":
tuple = OLD;
break;
default:
return;
}
if (tuple.subject == "skip") {
return null;
}
if (tuple.subject == "modify" && NEW) {
NEW.val = tuple.val * 2;
return NEW;
}
$$
LANGUAGE pljs;
CREATE TABLE trig_table (subject text, val int);
INSERT INTO trig_table VALUES('skip', 1);
CREATE TRIGGER test_trigger2
BEFORE INSERT OR UPDATE OR DELETE
ON trig_table FOR EACH ROW
EXECUTE PROCEDURE test_trigger2();
INSERT INTO trig_table VALUES
('skip', 1), ('modify', 2), ('noop', 3);
SELECT * FROM trig_table;
subject | val
---------+-----
skip | 1
modify | 4
noop | 3
(3 rows)
UPDATE trig_table SET val = 10;
SELECT * FROM trig_table;
subject | val
---------+-----
skip | 1
modify | 8
noop | 10
(3 rows)
DELETE FROM trig_table;
SELECT * FROM trig_table;
subject | val
---------+-----
skip | 1
(1 row)
|
