Thread: 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 #