CREATE_OR_REPLACE_TRIGGER_v01.patch
application/octet-stream
Filename: CREATE_OR_REPLACE_TRIGGER_v01.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
Series: patch v1
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/create_trigger.sgml | 1 | 1 |
| src/backend/catalog/heap.c | 2 | 1 |
| src/backend/catalog/index.c | 2 | 1 |
| src/backend/catalog/pg_constraint.c | 51 | 9 |
| src/backend/commands/tablecmds.c | 3 | 2 |
| src/backend/commands/trigger.c | 117 | 47 |
| src/backend/commands/typecmds.c | 2 | 1 |
| src/backend/parser/gram.y | 23 | 21 |
| src/include/catalog/pg_constraint.h | 2 | 1 |
| src/include/nodes/parsenodes.h | 1 | 0 |
| src/test/regress/expected/triggers.out | 388 | 0 |
| src/test/regress/sql/triggers.sql | 307 | 0 |
diff -ur b/doc/src/sgml/ref/create_trigger.sgml a/doc/src/sgml/ref/create_trigger.sgml
--- b/doc/src/sgml/ref/create_trigger.sgml 2019-02-28 06:04:32.776078441 +0000
+++ a/doc/src/sgml/ref/create_trigger.sgml 2019-02-28 06:38:01.628078441 +0000
@@ -26,7 +26,7 @@
<refsynopsisdiv>
<synopsis>
-CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] }
+CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] }
ON <replaceable class="parameter">table_name</replaceable>
[ FROM <replaceable class="parameter">referenced_table_name</replaceable> ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
Binary files b/.git/index and a/.git/index differ
diff -ur b/src/backend/catalog/heap.c a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c 2019-02-28 06:04:32.878078441 +0000
+++ a/src/backend/catalog/heap.c 2019-02-28 06:18:33.915078441 +0000
@@ -2375,7 +2375,8 @@
is_local, /* conislocal */
inhcount, /* coninhcount */
is_no_inherit, /* connoinherit */
- is_internal); /* internally constructed? */
+ is_internal, /* internally constructed? */
+ InvalidOid);
pfree(ccbin);
diff -ur b/src/backend/catalog/index.c a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c 2019-02-28 06:04:32.879078441 +0000
+++ a/src/backend/catalog/index.c 2019-02-28 06:18:29.702078441 +0000
@@ -1331,7 +1331,8 @@
islocal,
inhcount,
noinherit,
- is_internal);
+ is_internal,
+ InvalidOid);
/*
* Register the index as internally dependent on the constraint.
diff -ur b/src/backend/catalog/pg_constraint.c a/src/backend/catalog/pg_constraint.c
--- b/src/backend/catalog/pg_constraint.c 2019-02-28 06:04:32.882078441 +0000
+++ a/src/backend/catalog/pg_constraint.c 2019-02-28 06:19:43.451078441 +0000
@@ -76,10 +76,11 @@
bool conIsLocal,
int conInhCount,
bool conNoInherit,
- bool is_internal)
+ bool is_internal,
+ Oid existing_constraint_oid)
{
Relation conDesc;
- Oid conOid;
+ Oid conOid = InvalidOid;
HeapTuple tup;
bool nulls[Natts_pg_constraint];
Datum values[Natts_pg_constraint];
@@ -92,7 +93,11 @@
NameData cname;
int i;
ObjectAddress conobject;
-
+ SysScanDesc conscan;
+ ScanKeyData skey[2];
+ HeapTuple tuple;
+ bool replaces[Natts_pg_constraint];
+ Form_pg_constraint constrForm;
conDesc = table_open(ConstraintRelationId, RowExclusiveLock);
Assert(constraintName);
@@ -164,9 +169,11 @@
values[i] = (Datum) NULL;
}
- conOid = GetNewOidWithIndex(conDesc, ConstraintOidIndexId,
- Anum_pg_constraint_oid);
- values[Anum_pg_constraint_oid - 1] = ObjectIdGetDatum(conOid);
+ if(!existing_constraint_oid){
+ conOid = GetNewOidWithIndex(conDesc, ConstraintOidIndexId,
+ Anum_pg_constraint_oid);
+ values[Anum_pg_constraint_oid - 1] = ObjectIdGetDatum(conOid);
+ }
values[Anum_pg_constraint_conname - 1] = NameGetDatum(&cname);
values[Anum_pg_constraint_connamespace - 1] = ObjectIdGetDatum(constraintNamespace);
values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType);
@@ -220,9 +227,44 @@
else
nulls[Anum_pg_constraint_conbin - 1] = true;
- tup = heap_form_tuple(RelationGetDescr(conDesc), values, nulls);
-
- CatalogTupleInsert(conDesc, tup);
+ if (OidIsValid(existing_constraint_oid)){
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(existing_constraint_oid));
+
+ conscan = systable_beginscan(conDesc,
+ ConstraintOidIndexId,
+ true,
+ NULL,
+ 1,
+ skey);
+
+ tuple = systable_getnext(conscan);
+ Assert (HeapTupleIsValid(tuple));
+ constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ conOid = constrForm->oid;
+ Assert(conOid == existing_constraint_oid);
+
+ memset(replaces, true, sizeof(replaces));
+ replaces[Anum_pg_constraint_oid - 1] = false; /* skip updating Oid data */
+ replaces[Anum_pg_constraint_conname - 1] = false;
+ replaces[Anum_pg_constraint_confrelid - 1] = false;
+
+ /* Modify the existing constraint entry */
+ tup = heap_modify_tuple(tuple, RelationGetDescr(conDesc), values, nulls, replaces);
+ CatalogTupleUpdate(conDesc, &tuple->t_self, tup);
+ heap_freetuple(tup);
+
+ /* Remove all old dependencies before registering new ones */
+ deleteDependencyRecordsFor(ConstraintRelationId, conOid, true);
+
+ systable_endscan(conscan);
+ }else{
+ tup = heap_form_tuple(RelationGetDescr(conDesc), values, nulls);
+ CatalogTupleInsert(conDesc, tup);
+ heap_freetuple(tup);
+ }
conobject.classId = ConstraintRelationId;
conobject.objectId = conOid;
diff -ur b/src/backend/commands/tablecmds.c a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c 2019-02-28 06:04:32.905078441 +0000
+++ a/src/backend/commands/tablecmds.c 2019-02-28 06:12:34.362078441 +0000
@@ -7633,7 +7633,8 @@
true, /* islocal */
0, /* inhcount */
connoinherit, /* conNoInherit */
- false); /* is_internal */
+ false, /* is_internal */
+ InvalidOid);
ObjectAddressSet(address, ConstraintRelationId, constrOid);
/*
@@ -8016,7 +8017,7 @@
NULL,
NULL,
false,
- 1, false, true);
+ 1, false, true, InvalidOid);
subclone = lappend_oid(subclone, constrOid);
/* Set up partition dependencies for the new constraint */
diff -ur b/src/backend/commands/trigger.c a/src/backend/commands/trigger.c
--- b/src/backend/commands/trigger.c 2019-02-28 06:04:32.907078441 +0000
+++ a/src/backend/commands/trigger.c 2019-02-28 06:14:16.578078441 +0000
@@ -29,6 +29,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_depend.h"
#include "commands/dbcommands.h"
#include "commands/defrem.h"
#include "commands/trigger.h"
@@ -178,7 +179,7 @@
HeapTuple tuple;
Oid fargtypes[1]; /* dummy */
Oid funcrettype;
- Oid trigoid;
+ Oid trigoid = InvalidOid;
char internaltrigname[NAMEDATALEN];
char *trigname;
Oid constrrelid = InvalidOid;
@@ -188,6 +189,13 @@
char *newtablename = NULL;
bool partition_recurse;
+ bool is_update = false;
+ HeapTuple newtup;
+ TupleDesc tupDesc;
+ bool replaces[Natts_pg_trigger];
+ Oid existing_constraint_oid = InvalidOid;
+ bool trigger_exists = false;
+
if (OidIsValid(relOid))
rel = table_open(relOid, ShareRowExclusiveLock);
else
@@ -666,6 +674,59 @@
}
/*
+ * Generate the trigger's OID now, so that we can use it in the name if
+ * needed.
+ */
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&key,
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ NULL, 1, &key);
+ while (HeapTupleIsValid(tuple = systable_getnext(tgscan)))
+ {
+ Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(tuple);
+
+ if (namestrcmp(&(pg_trigger->tgname), stmt->trigname) == 0)
+ {
+ // trigoid = HeapTupleGetOid(tuple); // raw code
+ trigoid = pg_trigger->oid;
+ existing_constraint_oid = pg_trigger->tgconstraint;
+ trigger_exists = true;
+ break;
+ }
+ }
+ systable_endscan(tgscan);
+
+ if (!trigger_exists)
+ trigoid = GetNewOidWithIndex(tgrel, TriggerOidIndexId,
+ Anum_pg_trigger_oid);
+
+ /* Replacement between normal triggers and constraint triggers are restricted */
+ if (stmt->replace && trigger_exists)
+ {
+ if (stmt->isconstraint && !OidIsValid(existing_constraint_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("Trigger \"%s\" for relation \"%s\" cannot be replaced with constraint trigger",
+ stmt->trigname, RelationGetRelationName(rel))));
+ else if (!stmt->isconstraint && OidIsValid(existing_constraint_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("Constraint trigger \"%s\" for relation \"%s\" cannot be replaced with non-constraint trigger",
+ stmt->trigname, RelationGetRelationName(rel))));
+ }
+ else if (trigger_exists && !isInternal)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("trigger \"%s\" for relation \"%s\" already exists",
+ stmt->trigname, RelationGetRelationName(rel))));
+ }
+
+ /*
* Find and validate the trigger function.
*/
if (!OidIsValid(funcoid))
@@ -755,19 +816,11 @@
true, /* islocal */
0, /* inhcount */
true, /* isnoinherit */
- isInternal); /* is_internal */
+ isInternal, /* is_internal */
+ existing_constraint_oid);
}
/*
- * Generate the trigger's OID now, so that we can use it in the name if
- * needed.
- */
- tgrel = table_open(TriggerRelationId, RowExclusiveLock);
-
- trigoid = GetNewOidWithIndex(tgrel, TriggerOidIndexId,
- Anum_pg_trigger_oid);
-
- /*
* If trigger is internally generated, modify the provided trigger name to
* ensure uniqueness by appending the trigger OID. (Callers will usually
* supply a simple constant trigger name in these cases.)
@@ -785,37 +838,6 @@
}
/*
- * Scan pg_trigger for existing triggers on relation. We do this only to
- * give a nice error message if there's already a trigger of the same
- * name. (The unique index on tgrelid/tgname would complain anyway.) We
- * can skip this for internally generated triggers, since the name
- * modification above should be sufficient.
- *
- * NOTE that this is cool only because we have ShareRowExclusiveLock on
- * the relation, so the trigger set won't be changing underneath us.
- */
- if (!isInternal)
- {
- ScanKeyInit(&key,
- Anum_pg_trigger_tgrelid,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(RelationGetRelid(rel)));
- tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
- NULL, 1, &key);
- while (HeapTupleIsValid(tuple = systable_getnext(tgscan)))
- {
- Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(tuple);
-
- if (namestrcmp(&(pg_trigger->tgname), trigname) == 0)
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("trigger \"%s\" for relation \"%s\" already exists",
- trigname, RelationGetRelationName(rel))));
- }
- systable_endscan(tgscan);
- }
-
- /*
* Build the new pg_trigger tuple.
*
* When we're creating a trigger in a partition, we mark it as internal,
@@ -942,12 +964,51 @@
tuple = heap_form_tuple(tgrel->rd_att, values, nulls);
- /*
- * Insert tuple into pg_trigger.
- */
- CatalogTupleInsert(tgrel, tuple);
+ if (!trigger_exists)
+ {
+ tuple = heap_form_tuple(tgrel->rd_att, values, nulls);
- heap_freetuple(tuple);
+ /*
+ * Insert tuple into pg_trigger.
+ */
+ CatalogTupleInsert(tgrel, tuple);
+
+ heap_freetuple(tuple);
+ }
+ else
+ {
+ memset(replaces, true, sizeof(replaces));
+
+ ScanKeyInit(&key,
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ NULL, 1, &key);
+ while (HeapTupleIsValid(tuple = systable_getnext(tgscan)))
+ {
+ Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(tuple);
+
+ if (namestrcmp(&(pg_trigger->tgname), trigname) == 0)
+ {
+ tupDesc = RelationGetDescr(tgrel);
+ replaces[Anum_pg_trigger_oid - 1] = false; /* skip updating Oid data */
+ replaces[Anum_pg_trigger_tgrelid - 1] = false;
+ replaces[Anum_pg_trigger_tgname - 1] = false;
+ trigoid = pg_trigger->oid;
+ newtup = heap_modify_tuple(tuple, tupDesc, values, nulls, replaces);
+
+ /* Update tuple in pg_trigger */
+ CatalogTupleUpdate(tgrel, &tuple->t_self, newtup);
+
+ heap_freetuple(newtup);
+ is_update = true;
+ break;
+ }
+ }
+ systable_endscan(tgscan);
+ }
+
table_close(tgrel, RowExclusiveLock);
pfree(DatumGetPointer(values[Anum_pg_trigger_tgname - 1]));
@@ -990,6 +1051,15 @@
myself.objectId = trigoid;
myself.objectSubId = 0;
+ /*
+ * In case of replace trigger, trigger should no-more dependent on old
+ * referenced objects. Always remove the old dependencies and then
+ * register new ones. In that way, even if the old referenced object gets
+ * dropped, trigger will remain in the database.
+ */
+ if (is_update)
+ deleteDependencyRecordsFor(myself.classId, myself.objectId, true);
+
referenced.classId = ProcedureRelationId;
referenced.objectId = funcoid;
referenced.objectSubId = 0;
diff -ur b/src/backend/commands/typecmds.c a/src/backend/commands/typecmds.c
--- b/src/backend/commands/typecmds.c 2019-02-28 06:04:32.909078441 +0000
+++ a/src/backend/commands/typecmds.c 2019-02-28 06:14:54.554078441 +0000
@@ -3167,7 +3167,8 @@
true, /* is local */
0, /* inhcount */
false, /* connoinherit */
- false); /* is_internal */
+ false, /* is_internal */
+ InvalidOid);
if (constrAddr)
ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid);
diff -ur b/src/backend/parser/gram.y a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y 2019-02-28 06:04:32.977078441 +0000
+++ a/src/backend/parser/gram.y 2019-02-28 06:09:25.615078441 +0000
@@ -5271,48 +5271,50 @@
*****************************************************************************/
CreateTrigStmt:
- CREATE TRIGGER name TriggerActionTime TriggerEvents ON
+ CREATE opt_or_replace TRIGGER name TriggerActionTime TriggerEvents ON
qualified_name TriggerReferencing TriggerForSpec TriggerWhen
EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')'
{
CreateTrigStmt *n = makeNode(CreateTrigStmt);
- n->trigname = $3;
- n->relation = $7;
- n->funcname = $13;
- n->args = $15;
- n->row = $9;
- n->timing = $4;
- n->events = intVal(linitial($5));
- n->columns = (List *) lsecond($5);
- n->whenClause = $10;
- n->transitionRels = $8;
+ n->replace = $2;
+ n->trigname = $4;
+ n->relation = $8;
+ n->funcname = $14;
+ n->args = $16;
+ n->row = $10;
+ n->timing = $5;
+ n->events = intVal(linitial($6));
+ n->columns = (List *) lsecond($6);
+ n->whenClause = $11;
+ n->transitionRels = $9;
n->isconstraint = false;
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
$$ = (Node *)n;
}
- | CREATE CONSTRAINT TRIGGER name AFTER TriggerEvents ON
+ | CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
qualified_name OptConstrFromTable ConstraintAttributeSpec
FOR EACH ROW TriggerWhen
EXECUTE FUNCTION_or_PROCEDURE func_name '(' TriggerFuncArgs ')'
{
CreateTrigStmt *n = makeNode(CreateTrigStmt);
- n->trigname = $4;
- n->relation = $8;
- n->funcname = $17;
- n->args = $19;
+ n->replace = $2;
+ n->trigname = $5;
+ n->relation = $9;
+ n->funcname = $18;
+ n->args = $20;
n->row = true;
n->timing = TRIGGER_TYPE_AFTER;
- n->events = intVal(linitial($6));
- n->columns = (List *) lsecond($6);
- n->whenClause = $14;
+ n->events = intVal(linitial($7));
+ n->columns = (List *) lsecond($7);
+ n->whenClause = $15;
n->transitionRels = NIL;
n->isconstraint = true;
- processCASbits($10, @10, "TRIGGER",
+ processCASbits($11, @11, "TRIGGER",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
- n->constrrel = $9;
+ n->constrrel = $10;
$$ = (Node *)n;
}
;
diff -ur b/src/include/catalog/pg_constraint.h a/src/include/catalog/pg_constraint.h
--- b/src/include/catalog/pg_constraint.h 2019-02-28 06:04:33.462078441 +0000
+++ a/src/include/catalog/pg_constraint.h 2019-02-28 06:30:00.617078441 +0000
@@ -224,7 +224,8 @@
bool conIsLocal,
int conInhCount,
bool conNoInherit,
- bool is_internal);
+ bool is_internal,
+ Oid existing_constraint_oid);
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
diff -ur b/src/include/nodes/parsenodes.h a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h 2019-02-28 06:04:33.497078441 +0000
+++ a/src/include/nodes/parsenodes.h 2019-02-28 06:15:44.117078441 +0000
@@ -2401,6 +2401,7 @@
bool isconstraint; /* This is a constraint trigger */
/* explicitly named transition data */
List *transitionRels; /* TriggerTransition nodes, or NIL if none */
+ bool replace; /* T => replace if already exists */
/* The remaining fields are only used for constraint triggers */
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
diff -ur b/src/test/regress/expected/triggers.out a/src/test/regress/expected/triggers.out
--- b/src/test/regress/expected/triggers.out 2019-02-28 06:04:33.782078441 +0000
+++ a/src/test/regress/expected/triggers.out 2019-02-28 06:16:43.344078441 +0000
@@ -1715,6 +1715,394 @@
drop table self_ref_trigger;
drop function self_ref_trigger_ins_func();
drop function self_ref_trigger_del_func();
+create table my_table1 (id integer, name text);
+create table my_table2 (id integer);
+create function my_proc1() returns trigger as $$
+begin
+ return null;
+end;$$ language plpgsql;
+create function my_updateproc1() returns trigger as $$
+begin
+ update my_table2 set id=new.id where id=old.id;
+ return null;
+end;$$ language plpgsql;
+create function my_deleteproc1() returns trigger as $$
+begin
+ delete from my_table2 where id=old.id;
+ return null;
+end;$$ language plpgsql;
+create function my_insertproc1() returns trigger as $$
+begin
+ insert into my_table2 values(new.id);
+ return null;
+end;$$ language plpgsql;
+insert into my_table1 values(323, 'Alex');
+insert into my_table1 values(23, 'Teddy');
+insert into my_table1 values(38, 'Bob');
+insert into my_table2 values(323);
+insert into my_table2 values(23);
+insert into my_table2 values(38);
+-- Create regular trigger
+CREATE OR REPLACE TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_deleteproc1();
+--Expected Result: New trigger with the name my_regular_trigger created.
+delete from my_table1 where id=323;
+select * from my_table1;
+ id | name
+----+-------
+ 23 | Teddy
+ 38 | Bob
+(2 rows)
+
+select * from my_table2;
+ id
+----
+ 23
+ 38
+(2 rows)
+
+CREATE OR REPLACE CONSTRAINT TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_deleteproc1();
+--Expected Result: New constraint trigger with the name my_constraint_trigger created.
+delete from my_table1 where id=23;
+select * from my_table1;
+ id | name
+----+------
+ 38 | Bob
+(1 row)
+
+select * from my_table2;
+ id
+----
+ 38
+(1 row)
+
+CREATE OR REPLACE TRIGGER my_regular_trigger AFTER INSERT ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_insertproc1();
+--Expected Result: Replaces my_regular_trigger definition with new definition having event as INSERT.
+insert into my_table1 values(323, 'Alex');
+select * from my_table1;
+ id | name
+-----+------
+ 38 | Bob
+ 323 | Alex
+(2 rows)
+
+select * from my_table2;
+ id
+-----
+ 38
+ 323
+(2 rows)
+
+CREATE OR REPLACE CONSTRAINT TRIGGER my_constraint_trigger AFTER INSERT ON my_table1
+DEFERRABLE INITIALLY DEFERRED
+FOR EACH ROW
+EXECUTE PROCEDURE my_insertproc1();
+--Expected Result: Replaces my_constraint_trigger definition with new definition having event as INSERT.
+insert into my_table1 values(23, 'Teddy');
+select * from my_table1;
+ id | name
+-----+-------
+ 38 | Bob
+ 323 | Alex
+ 23 | Teddy
+(3 rows)
+
+select * from my_table2;
+ id
+-----
+ 38
+ 323
+ 23
+ 23
+(4 rows)
+
+CREATE CONSTRAINT TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+ERROR: trigger "my_regular_trigger" for relation "my_table1" already exists
+--Expected Result: Error message "Trigger 'my_regular_trigger' for relation 'my_table1' already exists" should be shown.
+CREATE OR REPLACE CONSTRAINT TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+ERROR: Trigger "my_regular_trigger" for relation "my_table1" cannot be replaced with constraint trigger
+--Expected Result: Error message "Trigger 'my_regular_trigger' for relation "my_table1" cannot be replaced with constraint trigger." should be shown.
+CREATE TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+ERROR: trigger "my_constraint_trigger" for relation "my_table1" already exists
+--Expected Result: Error message "Trigger 'my_constraint_trigger' for relation 'my_table' already exists" should be shown.
+CREATE OR REPLACE TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+ERROR: Constraint trigger "my_constraint_trigger" for relation "my_table1" cannot be replaced with non-constraint trigger
+--Expected Result: Error message "Constraint trigger 'my_constraint_trigger' for relation "my_table1" cannot be replaced with non-constraint trigger." should be shown.
+CREATE TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+EXECUTE PROCEDURE my_proc1();
+ERROR: trigger "my_regular_trigger" for relation "my_table1" already exists
+--Expected Result: Error message "Trigger 'my_regular_trigger' for relation 'my_table1' already exists" should be shown.
+CREATE CONSTRAINT TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+ERROR: trigger "my_constraint_trigger" for relation "my_table1" already exists
+--Expected Result: Error message "Trigger 'my_constraint_trigger' for relation 'my_table' already exists" should be shown.
+CREATE OR REPLACE TRIGGER my_trigger2 AFTER UPDATE OF NAME ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_updateproc1();
+CREATE OR REPLACE TRIGGER my_trigger2 AFTER DELETE ON my_table1
+EXECUTE PROCEDURE my_proc1();
+ALTER TABLE my_table1 DROP COLUMN name;
+DROP FUNCTION my_updateproc1();
+\d my_table1;
+ Table "public.my_table1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | |
+Triggers:
+ my_constraint_trigger AFTER INSERT ON my_table1 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION my_insertproc1()
+ my_regular_trigger AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE FUNCTION my_insertproc1()
+ my_trigger2 AFTER DELETE ON my_table1 FOR EACH STATEMENT EXECUTE FUNCTION my_proc1()
+
+CREATE TRIGGER my_trigger1 BEFORE DELETE ON my_table1
+EXECUTE PROCEDURE my_proc1();
+CREATE CONSTRAINT TRIGGER my_constraint_trigger2 AFTER INSERT ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+\h CREATE TRIGGER;
+Command: CREATE TRIGGER
+Description: define a new trigger
+Syntax:
+CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
+ ON table_name
+ [ FROM referenced_table_name ]
+ [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
+ [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
+ [ FOR [ EACH ] { ROW | STATEMENT } ]
+ [ WHEN ( condition ) ]
+ EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
+
+where event can be one of:
+
+ INSERT
+ UPDATE [ OF column_name [, ... ] ]
+ DELETE
+ TRUNCATE
+
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgname = 'my_trigger2';
+ pg_get_triggerdef
+-----------------------------------------------------------------------------------------------------
+ CREATE TRIGGER my_trigger2 AFTER DELETE ON my_table1 FOR EACH STATEMENT EXECUTE FUNCTION my_proc1()
+(1 row)
+
+----Clean up begin --------
+drop table my_table1;
+drop table my_table2;
+drop function my_deleteproc1();
+drop function my_insertproc1();
+drop function my_proc1();
+------Clean up end ---------
+------------------------------TESTCASE1-6 BEFORE TRIGGER---------------------------------------------------------
+---Check REPLACE against various before triggers
+CREATE TABLE my_table1 (id integer);
+CREATE VIEW my_view1 AS SELECT id from my_table1;
+create function firstproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST1 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function secondproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST2 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function thirdproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST3 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function someproc() returns trigger as $$
+begin
+ RAISE NOTICE 'Some Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+ --testcase1
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE firstproc();
+insert into my_table1 (id) values (1);
+NOTICE: TEST1 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 7 | O | f | f | f | 0 | | \x |
+(1 row)
+
+ --testcase2
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE secondproc();
+insert into my_table1 (id) values (1);
+NOTICE: TEST2 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 7 | O | f | f | f | 0 | | \x |
+(1 row)
+
+ --testcase3
+DROP TRIGGER sometrig on my_table1;
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE thirdproc();
+insert into my_table1 (id) values (1);
+NOTICE: TEST3 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 7 | O | f | f | f | 0 | | \x |
+(1 row)
+
+DROP TRIGGER sometrig on my_table1;
+----Clean up begin --------
+drop function firstproc();
+drop function secondproc();
+drop function thirdproc();
+drop function someproc();
+drop view my_view1;
+drop table my_table1;
+------Clean up end ---------
+------------------------------TESTCASE7-12 AFTER TRIGGER---------------------------------------------------------
+---Check REPLACE against various after triggers
+CREATE TABLE my_table1 (id integer);
+CREATE VIEW my_view1 AS SELECT id from my_table1;
+create function firstproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST7 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function secondproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST8 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function thirdproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST9 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function someproc() returns trigger as $$
+begin
+ RAISE NOTICE 'Some Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+ --testcase7
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE firstproc();
+insert into my_table1 (id) values (1);
+NOTICE: TEST7 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 5 | O | f | f | f | 0 | | \x |
+(1 row)
+
+ --testcase8
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE secondproc();
+insert into my_table1 (id) values (1);
+NOTICE: TEST8 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 5 | O | f | f | f | 0 | | \x |
+(1 row)
+
+ --testcase9
+DROP TRIGGER sometrig on my_table1;
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE thirdproc();
+insert into my_table1 (id) values (1);
+NOTICE: TEST9 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 5 | O | f | f | f | 0 | | \x |
+(1 row)
+
+DROP TRIGGER sometrig on my_table1;
+----Clean up begin --------
+drop function firstproc();
+drop function secondproc();
+drop function thirdproc();
+drop function someproc();
+drop view my_view1;
+drop table my_table1;
+------Clean up end ---------
+------------------------------TESTCASE13-18 BEFORE TRIGGER---------------------------------------------------------
+---Check REPLACE against various INSTEAD triggers
+CREATE TABLE my_table1 (id integer);
+CREATE VIEW my_view1 AS SELECT id from my_table1;
+create function firstproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST13 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function secondproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST14 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function thirdproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST15 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+create function someproc() returns trigger as $$
+begin
+ RAISE NOTICE 'Some Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+--testcase13
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE firstproc();
+insert into my_view1 (id) values (1);
+NOTICE: TEST13 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 69 | O | f | f | f | 0 | | \x |
+(1 row)
+
+--testcase14
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE secondproc();
+insert into my_view1 (id) values (1);
+NOTICE: TEST14 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 69 | O | f | f | f | 0 | | \x |
+(1 row)
+
+--testcase15
+DROP TRIGGER sometrig on my_table1;
+ERROR: trigger "sometrig" for table "my_table1" does not exist
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE thirdproc();
+insert into my_view1 (id) values (1);
+NOTICE: TEST15 Trigger
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ tgname | tgtype | tgenabled | tgisinternal | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
+----------+--------+-----------+--------------+--------------+----------------+---------+--------+--------+--------
+ sometrig | 69 | O | f | f | f | 0 | | \x |
+(1 row)
+
+----Clean up begin --------
+drop view my_view1;
+drop table my_table1;
+drop function firstproc();
+drop function secondproc();
+drop function thirdproc();
+drop function someproc();
+------Clean up end ---------
--
-- Check that statement triggers work correctly even with all children excluded
--
diff -ur b/src/test/regress/sql/triggers.sql a/src/test/regress/sql/triggers.sql
--- b/src/test/regress/sql/triggers.sql 2019-02-28 06:04:33.825078441 +0000
+++ a/src/test/regress/sql/triggers.sql 2019-02-28 06:18:56.615078441 +0000
@@ -1177,6 +1177,313 @@
drop function self_ref_trigger_ins_func();
drop function self_ref_trigger_del_func();
+create table my_table1 (id integer, name text);
+create table my_table2 (id integer);
+create function my_proc1() returns trigger as $$
+begin
+ return null;
+end;$$ language plpgsql;
+
+create function my_updateproc1() returns trigger as $$
+begin
+ update my_table2 set id=new.id where id=old.id;
+ return null;
+end;$$ language plpgsql;
+create function my_deleteproc1() returns trigger as $$
+begin
+ delete from my_table2 where id=old.id;
+ return null;
+end;$$ language plpgsql;
+
+create function my_insertproc1() returns trigger as $$
+begin
+ insert into my_table2 values(new.id);
+ return null;
+end;$$ language plpgsql;
+
+insert into my_table1 values(323, 'Alex');
+insert into my_table1 values(23, 'Teddy');
+insert into my_table1 values(38, 'Bob');
+insert into my_table2 values(323);
+insert into my_table2 values(23);
+insert into my_table2 values(38);
+-- Create regular trigger
+CREATE OR REPLACE TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_deleteproc1();
+--Expected Result: New trigger with the name my_regular_trigger created.
+
+delete from my_table1 where id=323;
+select * from my_table1;
+select * from my_table2;
+
+CREATE OR REPLACE CONSTRAINT TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_deleteproc1();
+--Expected Result: New constraint trigger with the name my_constraint_trigger created.
+
+delete from my_table1 where id=23;
+select * from my_table1;
+select * from my_table2;
+
+CREATE OR REPLACE TRIGGER my_regular_trigger AFTER INSERT ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_insertproc1();
+--Expected Result: Replaces my_regular_trigger definition with new definition having event as INSERT.
+
+insert into my_table1 values(323, 'Alex');
+select * from my_table1;
+select * from my_table2;
+
+
+CREATE OR REPLACE CONSTRAINT TRIGGER my_constraint_trigger AFTER INSERT ON my_table1
+DEFERRABLE INITIALLY DEFERRED
+FOR EACH ROW
+EXECUTE PROCEDURE my_insertproc1();
+--Expected Result: Replaces my_constraint_trigger definition with new definition having event as INSERT.
+
+insert into my_table1 values(23, 'Teddy');
+select * from my_table1;
+select * from my_table2;
+
+CREATE CONSTRAINT TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+--Expected Result: Error message "Trigger 'my_regular_trigger' for relation 'my_table1' already exists" should be shown.
+
+CREATE OR REPLACE CONSTRAINT TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+--Expected Result: Error message "Trigger 'my_regular_trigger' for relation "my_table1" cannot be replaced with constraint trigger." should be shown.
+
+CREATE TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+--Expected Result: Error message "Trigger 'my_constraint_trigger' for relation 'my_table' already exists" should be shown.
+
+CREATE OR REPLACE TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+--Expected Result: Error message "Constraint trigger 'my_constraint_trigger' for relation "my_table1" cannot be replaced with non-constraint trigger." should be shown.
+
+CREATE TRIGGER my_regular_trigger AFTER DELETE ON my_table1
+EXECUTE PROCEDURE my_proc1();
+--Expected Result: Error message "Trigger 'my_regular_trigger' for relation 'my_table1' already exists" should be shown.
+
+CREATE CONSTRAINT TRIGGER my_constraint_trigger AFTER DELETE ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+--Expected Result: Error message "Trigger 'my_constraint_trigger' for relation 'my_table' already exists" should be shown.
+
+CREATE OR REPLACE TRIGGER my_trigger2 AFTER UPDATE OF NAME ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_updateproc1();
+
+CREATE OR REPLACE TRIGGER my_trigger2 AFTER DELETE ON my_table1
+EXECUTE PROCEDURE my_proc1();
+
+ALTER TABLE my_table1 DROP COLUMN name;
+DROP FUNCTION my_updateproc1();
+\d my_table1;
+
+CREATE TRIGGER my_trigger1 BEFORE DELETE ON my_table1
+EXECUTE PROCEDURE my_proc1();
+
+CREATE CONSTRAINT TRIGGER my_constraint_trigger2 AFTER INSERT ON my_table1
+FOR EACH ROW
+EXECUTE PROCEDURE my_proc1();
+
+\h CREATE TRIGGER;
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgname = 'my_trigger2';
+----Clean up begin --------
+
+drop table my_table1;
+drop table my_table2;
+drop function my_deleteproc1();
+drop function my_insertproc1();
+drop function my_proc1();
+------Clean up end ---------
+
+------------------------------TESTCASE1-6 BEFORE TRIGGER---------------------------------------------------------
+---Check REPLACE against various before triggers
+
+CREATE TABLE my_table1 (id integer);
+
+CREATE VIEW my_view1 AS SELECT id from my_table1;
+
+create function firstproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST1 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function secondproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST2 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function thirdproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST3 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function someproc() returns trigger as $$
+begin
+ RAISE NOTICE 'Some Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+ --testcase1
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE firstproc();
+
+insert into my_table1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ --testcase2
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE secondproc();
+
+insert into my_table1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ --testcase3
+DROP TRIGGER sometrig on my_table1;
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig BEFORE INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE thirdproc();
+
+insert into my_table1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+
+DROP TRIGGER sometrig on my_table1;
+----Clean up begin --------
+drop function firstproc();
+drop function secondproc();
+drop function thirdproc();
+drop function someproc();
+drop view my_view1;
+drop table my_table1;
+------Clean up end ---------
+------------------------------TESTCASE7-12 AFTER TRIGGER---------------------------------------------------------
+---Check REPLACE against various after triggers
+
+CREATE TABLE my_table1 (id integer);
+
+CREATE VIEW my_view1 AS SELECT id from my_table1;
+
+create function firstproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST7 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function secondproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST8 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function thirdproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST9 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function someproc() returns trigger as $$
+begin
+ RAISE NOTICE 'Some Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+ --testcase7
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE firstproc();
+
+insert into my_table1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ --testcase8
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE secondproc();
+
+insert into my_table1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+ --testcase9
+DROP TRIGGER sometrig on my_table1;
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig AFTER INSERT ON my_table1 FOR EACH ROW EXECUTE PROCEDURE thirdproc();
+
+insert into my_table1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+
+DROP TRIGGER sometrig on my_table1;
+----Clean up begin --------
+drop function firstproc();
+drop function secondproc();
+drop function thirdproc();
+drop function someproc();
+drop view my_view1;
+drop table my_table1;
+------Clean up end ---------
+
+
+------------------------------TESTCASE13-18 BEFORE TRIGGER---------------------------------------------------------
+---Check REPLACE against various INSTEAD triggers
+
+CREATE TABLE my_table1 (id integer);
+
+CREATE VIEW my_view1 AS SELECT id from my_table1;
+
+create function firstproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST13 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function secondproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST14 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function thirdproc() returns trigger as $$
+begin
+ RAISE NOTICE 'TEST15 Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+create function someproc() returns trigger as $$
+begin
+ RAISE NOTICE 'Some Trigger';
+ RETURN null;
+end;$$ language plpgsql;
+
+--testcase13
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE firstproc();
+
+insert into my_view1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+
+--testcase14
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE secondproc();
+
+insert into my_view1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+
+--testcase15
+DROP TRIGGER sometrig on my_table1;
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE someproc();
+CREATE OR REPLACE TRIGGER sometrig INSTEAD OF INSERT ON my_view1 FOR EACH ROW EXECUTE PROCEDURE thirdproc();
+
+insert into my_view1 (id) values (1);
+select tgname,tgtype,tgenabled,tgisinternal,tgdeferrable,tginitdeferred,tgnargs,tgattr,tgargs,tgqual from pg_trigger where tgname='sometrig';
+
+----Clean up begin --------
+drop view my_view1;
+drop table my_table1;
+drop function firstproc();
+drop function secondproc();
+drop function thirdproc();
+drop function someproc();
+------Clean up end ---------
--
-- Check that statement triggers work correctly even with all children excluded
--