Need help : Weird referencial Integrity triggers
I've been changing some tables and I saw a strange behavior in pg_trigger.
Check out the commented code below :
I will create two tables and later I'll make a foreing key from
prog_tabelanumero to prog_gruponumero :
CREATE TABLE prog_gruponumero (
cod_gruponumero integer NOT NULL,
descricao varchar(30) NOT NULL,
CONSTRAINT XPKprog_gruponumer
PRIMARY KEY (cod_gruponumero)
);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'xpkprog_gruponumer' for table 'prog_gruponumero'
CREATE
CREATE TABLE prog_tabelanumero (
cod_gruponumero integer NOT NULL,
numero varchar(25) NOT NULL,
CONSTRAINT XPKprog_tabelanume
PRIMARY KEY (cod_gruponumero, numero)
);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'xpkprog_tabelanume' for table 'prog_tabelanumero'
CREATE
ALTER TABLE prog_tabelanumero
ADD CONSTRAINT RGrupoXNumero FOREIGN KEY (cod_gruponumero)
REFERENCES prog_gruponumero
ON DELETE CASCADE;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
Now, if I select the corresponding triggers from pg_trigger, I'll find
three. Why 3? One for update, one for insert and one for delete ?
SELECT * FROM pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled |
tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
tginitdeferred | tgnargs | tgattr |
tgargs
---------+-----------------------------+--------+--------+-----------+------
----------+---------------+---------------+--------------+----------------+-
--------+--------+------
----------------------------------------------------------------------------
-------------------------------
1260 | pg_sync_pg_pwd | 12 | 29 | t | f
| | 0 | f | f | 0 |
|
430893 | RI_ConstraintTrigger_430906 | 1644 | 21 | t | t
| rgrupoxnumero | 430880 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430880 | RI_ConstraintTrigger_430908 | 1646 | 9 | t | t
| rgrupoxnumero | 430893 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430880 | RI_ConstraintTrigger_430910 | 1655 | 17 | t | t
| rgrupoxnumero | 430893 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
(4 rows)
Now the strange thing I said : If I make that alter table again, I'll get 6
triggers. There are no checking if triggers already exists. Any comments
about this?
ALTER TABLE prog_tabelanumero
ADD CONSTRAINT RGrupoXNumero FOREIGN KEY (cod_gruponumero)
REFERENCES prog_gruponumero
ON DELETE CASCADE;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
SELECT * FROM pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled |
tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
tginitdeferred | tgnargs | tgattr |
tgargs
---------+-----------------------------+--------+--------+-----------+------
----------+---------------+---------------+--------------+----------------+-
--------+--------+------
----------------------------------------------------------------------------
-------------------------------
1260 | pg_sync_pg_pwd | 12 | 29 | t | f
| | 0 | f | f | 0
| |
430893 | RI_ConstraintTrigger_430906 | 1644 | 21 | t | t
| rgrupoxnumero | 430880 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430880 | RI_ConstraintTrigger_430908 | 1646 | 9 | t | t
| rgrupoxnumero | 430893 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430880 | RI_ConstraintTrigger_430910 | 1655 | 17 | t | t
| rgrupoxnumero | 430893 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430893 | RI_ConstraintTrigger_430944 | 1644 | 21 | t | t
| rgrupoxnumero | 430880 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430880 | RI_ConstraintTrigger_430946 | 1646 | 9 | t | t
| rgrupoxnumero | 430893 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
430880 | RI_ConstraintTrigger_430948 | 1655 | 17 | t | t
| rgrupoxnumero | 430893 | f | f | 6 |
| rgrup
oxnumero\000prog_tabelanumero\000prog_gruponumero\000UNSPECIFIED\000cod_grup
onumero\000cod_gruponumero\000
(7 rows)
Vilson farias wrote:
I've been changing some tables and I saw a strange behavior in pg_trigger.
Check out the commented code below :[...]
Now, if I select the corresponding triggers from pg_trigger, I'll find
three. Why 3? One for update, one for insert and one for delete ?
One for INSERT/UPDATE of the foreign key table, one for
DELETE and one for UPDATE of the primary key table.
You haven't specified a referential action for the UPDATE
case of the primary key (ON UPDATE ...). The default action
is NO ACTION, what means that changing the primary keys value
isn't allowed as long as there are references to it from any
foreign key. Thus, updating a PK can never be ignored and
there must be a trigger.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #