4
create table pkeys (pkey1 int4 not null, pkey2 text not null);
5
create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
6
create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
7
create index fkeys_i on fkeys (fkey1, fkey2);
8
create index fkeys2_i on fkeys2 (fkey21, fkey22);
9
create index fkeys2p_i on fkeys2 (pkey23);
10
insert into pkeys values (10, '1');
11
insert into pkeys values (20, '2');
12
insert into pkeys values (30, '3');
13
insert into pkeys values (40, '4');
14
insert into pkeys values (50, '5');
15
insert into pkeys values (60, '6');
16
create unique index pkeys_i on pkeys (pkey1, pkey2);
19
-- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
20
-- (fkey3) --> fkeys2 (pkey23)
22
create trigger check_fkeys_pkey_exist
23
before insert or update on fkeys
26
check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
27
create trigger check_fkeys_pkey2_exist
28
before insert or update on fkeys
30
execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
33
-- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
35
create trigger check_fkeys2_pkey_exist
36
before insert or update on fkeys2
39
check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
41
COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
42
ERROR: trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist
43
COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
44
COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
47
-- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
48
-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
50
create trigger check_pkeys_fkey_cascade
51
before delete or update on pkeys
54
check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
55
'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
58
-- ON DELETE/UPDATE (pkey23) RESTRICT:
61
create trigger check_fkeys2_fkey_restrict
62
before delete or update on fkeys2
64
execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
65
insert into fkeys2 values (10, '1', 1);
66
insert into fkeys2 values (30, '3', 2);
67
insert into fkeys2 values (40, '4', 5);
68
insert into fkeys2 values (50, '5', 3);
70
insert into fkeys2 values (70, '5', 3);
71
ERROR: tuple references non-existent key
72
DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
73
insert into fkeys values (10, '1', 2);
74
insert into fkeys values (30, '3', 3);
75
insert into fkeys values (40, '4', 2);
76
insert into fkeys values (50, '5', 2);
78
insert into fkeys values (70, '5', 1);
79
ERROR: tuple references non-existent key
80
DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
82
insert into fkeys values (60, '6', 4);
83
ERROR: tuple references non-existent key
84
DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
85
delete from pkeys where pkey1 = 30 and pkey2 = '3';
86
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
87
ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
88
CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
89
delete from pkeys where pkey1 = 40 and pkey2 = '4';
90
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
91
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
92
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
93
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
94
ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
95
CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
96
update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
97
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
98
NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
102
-- -- I've disabled the funny_dup17 test because the new semantics
103
-- -- of AFTER ROW triggers, which get now fired at the end of a
104
-- -- query always, cause funny_dup17 to enter an endless loop.
108
-- create table dup17 (x int4);
110
-- create trigger dup17_before
111
-- before insert on dup17
117
-- insert into dup17 values (17);
118
-- select count(*) from dup17;
119
-- insert into dup17 values (17);
120
-- select count(*) from dup17;
122
-- drop trigger dup17_before on dup17;
124
-- create trigger dup17_after
125
-- after insert on dup17
130
-- insert into dup17 values (13);
131
-- select count(*) from dup17 where x = 13;
132
-- insert into dup17 values (13);
133
-- select count(*) from dup17 where x = 13;
136
create sequence ttdummy_seq increment 10 start 0 minvalue 0;
137
create table tttest (
141
price_off int4 default 999999
143
create trigger ttdummy
144
before delete or update on tttest
147
ttdummy (price_on, price_off);
148
create trigger ttserial
149
before insert or update on tttest
152
autoinc (price_on, ttdummy_seq);
153
insert into tttest values (1, 1, null);
154
insert into tttest values (2, 2, null);
155
insert into tttest values (3, 3, 0);
156
select * from tttest;
157
price_id | price_val | price_on | price_off
158
----------+-----------+----------+-----------
164
delete from tttest where price_id = 2;
165
select * from tttest;
166
price_id | price_val | price_on | price_off
167
----------+-----------+----------+-----------
174
-- get current prices
175
select * from tttest where price_off = 999999;
176
price_id | price_val | price_on | price_off
177
----------+-----------+----------+-----------
182
-- change price for price_id == 3
183
update tttest set price_val = 30 where price_id = 3;
184
select * from tttest;
185
price_id | price_val | price_on | price_off
186
----------+-----------+----------+-----------
193
-- now we want to change pric_id in ALL tuples
194
-- this gets us not what we need
195
update tttest set price_id = 5 where price_id = 3;
196
select * from tttest;
197
price_id | price_val | price_on | price_off
198
----------+-----------+----------+-----------
206
-- restore data as before last update:
207
select set_ttdummy(0);
213
delete from tttest where price_id = 5;
214
update tttest set price_off = 999999 where price_val = 30;
215
select * from tttest;
216
price_id | price_val | price_on | price_off
217
----------+-----------+----------+-----------
224
-- and try change price_id now!
225
update tttest set price_id = 5 where price_id = 3;
226
select * from tttest;
227
price_id | price_val | price_on | price_off
228
----------+-----------+----------+-----------
235
-- isn't it what we need ?
236
select set_ttdummy(1);
242
-- we want to correct some "date"
243
update tttest set price_on = -1 where price_id = 1;
244
ERROR: ttdummy (tttest): you can't change price_on and/or price_off columns (use set_ttdummy)
245
-- but this doesn't work
247
select set_ttdummy(0);
253
update tttest set price_on = -1 where price_id = 1;
254
select * from tttest;
255
price_id | price_val | price_on | price_off
256
----------+-----------+----------+-----------
263
-- isn't it what we need ?
264
-- get price for price_id == 5 as it was @ "date" 35
265
select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
266
price_id | price_val | price_on | price_off
267
----------+-----------+----------+-----------
272
drop sequence ttdummy_seq;
274
-- tests for per-statement triggers
276
CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
277
CREATE TABLE main_table (a int, b int);
278
COPY main_table (a,b) FROM stdin;
279
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE 'plpgsql' AS '
281
RAISE NOTICE ''trigger_func() called: action = %, when = %, level = %'', TG_OP, TG_WHEN, TG_LEVEL;
284
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
285
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
286
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
287
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
289
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
290
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
292
CREATE TRIGGER before_upd_stmt_trig AFTER UPDATE ON main_table
293
EXECUTE PROCEDURE trigger_func();
294
CREATE TRIGGER before_upd_row_trig AFTER UPDATE ON main_table
295
FOR EACH ROW EXECUTE PROCEDURE trigger_func();
296
INSERT INTO main_table DEFAULT VALUES;
297
NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT
298
NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT
299
UPDATE main_table SET a = a + 1 WHERE b < 30;
300
NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
301
NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
302
NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
303
NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = ROW
304
NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT
305
-- UPDATE that effects zero rows should still call per-statement trigger
306
UPDATE main_table SET a = a + 2 WHERE b > 100;
307
NOTICE: trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT
308
-- COPY should fire per-row and per-statement INSERT triggers
309
COPY main_table (a, b) FROM stdin;
310
NOTICE: trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT
311
NOTICE: trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT
312
SELECT * FROM main_table ORDER BY a, b;