Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commitf186139

Browse files
committed
README for refint.c and example of using.
1 parentc853ca0 commitf186139

File tree

3 files changed

+121
-1
lines changed

3 files changed

+121
-1
lines changed

‎contrib/spi/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@ TARGETS= refint$(DLSUFFIX) refint.sql
1414
CLEANFILES+=$(TARGETS)
1515

1616
all::$(TARGETS)
17+
rm -f*.obj*.pic
1718

1819
%.sql:%.source
1920
rm -f$@;\
@@ -22,4 +23,4 @@ all:: $(TARGETS)
2223
-e"s:_DLSUFFIX_:$(DLSUFFIX):g"<$<>$@
2324

2425
clean:
25-
rm -f$(TARGETS)refint.o
26+
rm -f$(TARGETS)*.[op]*

‎contrib/spi/README

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
2+
Here are general trigger functions provided as workable examples
3+
of using SPI and triggers. "General" means that functions may be
4+
used for defining triggers for any tables but you have to specify
5+
table/field names (as described below) while creating a trigger.
6+
7+
1. refint.c - functions for implementing referential integrity.
8+
9+
check_primary_key () is to used for foreign keys of a table.
10+
11+
You are to create trigger (BEFORE INSERT OR UPDATE) using this
12+
function on a table referencing another table. You are to specify
13+
as function arguments: triggered table column names which correspond
14+
to foreign key, referenced table name and column names in referenced
15+
table which correspond to primary/unique key.
16+
You may create as many triggers as you need - one trigger for
17+
one reference.
18+
19+
check_foreign_key () is to used for primary/unique keys of a table.
20+
21+
You are to create trigger (BEFORE DELETE OR UPDATE) using this
22+
function on a table referenced by another table(s). You are to specify
23+
as function arguments: number of references for which function has to
24+
performe checking, action if referencing key found ('cascade' - to delete
25+
corresponding foreign key, 'restrict' - to abort transaction if foreign keys
26+
exist, 'setnull' - to set foreign key referencing primary/unique key
27+
being deleted to null), triggered table column names which correspond
28+
to primary/unique key, referencing table name and column names corresponding
29+
to foreign key (, ... - as many referencing tables/keys as specified
30+
by first argument).
31+
Note, that NOT NULL constraint and unique index have to be defined
32+
by youself.
33+
34+
There are examples in refint.example and regression tests
35+
(sql/triggers.sql).
36+
37+

‎contrib/spi/refint.example

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
--Column ID of table A is primary key:
2+
3+
CREATE TABLE A (
4+
IDint4 not null
5+
);
6+
CREATE UNIQUE INDEX AI ON A (ID);
7+
8+
--Columns REFB of table B and REFC of C are foreign keys referenting ID of A:
9+
10+
CREATE TABLE B (
11+
REFBint4
12+
);
13+
CREATE INDEX BI ON B (REFB);
14+
15+
CREATE TABLE C (
16+
REFCint4
17+
);
18+
CREATE INDEX CI ON C (REFC);
19+
20+
--Trigger for table A:
21+
22+
CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
23+
EXECUTE PROCEDURE
24+
check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
25+
/*
26+
2- means that check must be performed for foreign keys of 2 tables.
27+
cascade- defines that corresponding keys must be deleted.
28+
ID- name of primary key column in triggered table (A). You may
29+
use as many columns as you need.
30+
B- name of (first) table with foreign keys.
31+
REFB- name of foreign key column in this table. You may use as many
32+
columns as you need, but number of key columns in referenced
33+
table (A) must be the same.
34+
C- name of second table with foreign keys.
35+
REFC- name of foreign key column in this table.
36+
*/
37+
38+
--Trigger for table B:
39+
40+
CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
41+
EXECUTE PROCEDURE
42+
check_primary_key ('REFB', 'A', 'ID');
43+
44+
/*
45+
REFB- name of foreign key column in triggered (B) table. You may use as
46+
many columns as you need, but number of key columns in referenced
47+
table must be the same.
48+
A- referenced table name.
49+
ID- name of primary key column in referenced table.
50+
*/
51+
52+
--Trigger for table C:
53+
54+
CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
55+
EXECUTE PROCEDURE
56+
check_primary_key ('REFC', 'A', 'ID');
57+
58+
-- Now try
59+
60+
INSERT INTO A VALUES (10);
61+
INSERT INTO A VALUES (20);
62+
INSERT INTO A VALUES (30);
63+
INSERT INTO A VALUES (40);
64+
INSERT INTO A VALUES (50);
65+
66+
INSERT INTO B VALUES (1);-- invalid reference
67+
INSERT INTO B VALUES (10);
68+
INSERT INTO B VALUES (30);
69+
INSERT INTO B VALUES (30);
70+
71+
INSERT INTO C VALUES (11);-- invalid reference
72+
INSERT INTO C VALUES (20);
73+
INSERT INTO C VALUES (20);
74+
INSERT INTO C VALUES (30);
75+
76+
DELETE FROM A WHERE ID = 10;
77+
DELETE FROM A WHERE ID = 20;
78+
DELETE FROM A WHERE ID = 30;
79+
80+
SELECT * FROM A;
81+
SELECT * FROM B;
82+
SELECT * FROM C;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp