CREATE_OR_REPLACE_TRIGGER_v02.patch
application/octet-stream
Filename: CREATE_OR_REPLACE_TRIGGER_v02.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
Series: patch v2
| 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 | 6 | 3 |
| 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 | 3 | 1 |
| src/include/nodes/parsenodes.h | 1 | 0 |
| src/test/regress/expected/triggers.out | 390 | 0 |
| src/test/regress/sql/triggers.sql | 307 | 0 |
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 3339a4b..65aeea3 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<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 ] ]
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 3b8c8b1..45bd58c 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2400,7 +2400,8 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
is_local, /* conislocal */
inhcount, /* coninhcount */
is_no_inherit, /* connoinherit */
- is_internal); /* internally constructed? */
+ is_internal, /* internally constructed? */
+ InvalidOid);
pfree(ccbin);
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index bb60b23..ad5ec0e 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1770,7 +1770,8 @@ index_constraint_create(Relation heapRelation,
islocal,
inhcount,
noinherit,
- is_internal);
+ is_internal,
+ InvalidOid);
/*
* Register the index as internally dependent on the constraint.
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index b614559..067e129 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -76,10 +76,11 @@ CreateConstraintEntry(const char *constraintName,
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 @@ CreateConstraintEntry(const char *constraintName,
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 @@ CreateConstraintEntry(const char *constraintName,
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 @@ CreateConstraintEntry(const char *constraintName,
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 --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3aee2d8..9bfdfab 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8083,7 +8083,8 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel,
conislocal, /* islocal */
coninhcount, /* inhcount */
connoinherit, /* conNoInherit */
- false); /* is_internal */
+ false, /* is_internal */
+ InvalidOid);
ObjectAddressSet(address, ConstraintRelationId, constrOid);
@@ -8353,7 +8354,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
false,
1,
false,
- false);
+ false,
+ InvalidOid);
/*
* Give this constraint partition-type dependencies on the parent
@@ -8750,7 +8752,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
false, /* islocal */
1, /* inhcount */
false, /* conNoInherit */
- true);
+ true,
+ InvalidOid);
/* Set up partition dependencies for the new constraint */
ObjectAddressSet(address, ConstraintRelationId, constrOid);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 316692b..5025156 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -31,6 +31,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"
@@ -181,7 +182,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
HeapTuple tuple;
Oid fargtypes[1]; /* dummy */
Oid funcrettype;
- Oid trigoid;
+ Oid trigoid = InvalidOid;
char internaltrigname[NAMEDATALEN];
char *trigname;
Oid constrrelid = InvalidOid;
@@ -191,6 +192,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
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
@@ -687,6 +695,59 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
}
/*
+ * 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))
@@ -776,19 +837,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
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.)
@@ -806,37 +859,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
}
/*
- * 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,
@@ -963,12 +985,51 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
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]));
@@ -1011,6 +1072,15 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
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 --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index e9c8873..f4b414a 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -3174,7 +3174,8 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
true, /* is local */
0, /* inhcount */
false, /* connoinherit */
- false); /* is_internal */
+ false, /* is_internal */
+ InvalidOid);
if (constrAddr)
ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 208b4a1..b9e9543 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5322,48 +5322,50 @@ am_type:
*****************************************************************************/
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 --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index c1e60c7..f0f00b5 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -211,7 +211,9 @@ extern Oid CreateConstraintEntry(const char *constraintName,
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 --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 94ded3c..e2da9f6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2418,6 +2418,7 @@ typedef struct CreateTrigStmt
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 --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index cd2b550..17125be 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1715,6 +1715,396 @@ select * from self_ref_trigger;
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
+
+URL: https://www.postgresql.org/docs/devel/sql-createtrigger.html
+
+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 --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 8f833b7..c0fce2e 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1177,6 +1177,313 @@ 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;
+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
--