SQLite

** This is called by the parser when it sees a CREATE TRIGGER statement. See
** comments surrounding struct Trigger in sqliteInt.h for a description of 
** how triggers are stored.
*/
void sqliteCreateTrigger(
  Parse *pParse,      /* The parse context of the CREATE TRIGGER statement */
  Token *pName,       /* The name of the trigger */
  int tr_tm,          /* One of TK_BEFORE, TK_AFTER , TK_INSTEAD */
  int op,             /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
  IdList *pColumns,   /* column list if this is an UPDATE OF trigger */
  Token *pTableName,  /* The name of the table/view the trigger applies to */
  int foreach,        /* One of TK_ROW or TK_STATEMENT */
  Expr *pWhen,        /* WHEN clause */
  TriggerStep *pStepList, /* The triggered program */
  char const *zData,  /* The string data to make persistent */
  int zDataLen
){
  Trigger *nt;
  Table   *tab;
  int offset;
  TriggerStep *ss;

  /* Check that: 
  ** 1. the trigger name does not already exist.
  ** 2. the table (or view) does exist.
  ** 3. that we are not trying to create a trigger on the sqlite_master table
  ** 4. That we are not trying to create an INSTEAD OF trigger on a table.
  ** 5. That we are not trying to create a BEFORE or AFTER trigger on a view.
  */
  {
    char *tmp_str = sqliteStrNDup(pName->z, pName->n);
    if( sqliteHashFind(&(pParse->db->trigHash), tmp_str, pName->n + 1) ){
      sqliteSetNString(&pParse->zErrMsg, "trigger ", -1,
          pName->z, pName->n, " already exists", -1, 0);
      sqliteFree(tmp_str);

    }
    if( sqliteStrICmp(tab->zName, MASTER_NAME)==0 ){
      sqliteSetString(&pParse->zErrMsg, "cannot create trigger on system "
         "table: " MASTER_NAME, 0);
      pParse->nErr++;
      goto trigger_cleanup;
    }
    if( tab->pSelect && tr_tm != TK_INSTEAD ){
      sqliteSetNString(&pParse->zErrMsg, "cannot create ", -1,
	  (tr_tm == TK_BEFORE)?"BEFORE":"AFTER", -1, " trigger on view: ", -1
          , pTableName->z, pTableName->n, 0);
      goto trigger_cleanup;
    }
    if( !tab->pSelect && tr_tm == TK_INSTEAD ){
      sqliteSetNString(&pParse->zErrMsg, "cannot create INSTEAD OF", -1, 
	  " trigger on table: ", -1, pTableName->z, pTableName->n, 0);
      goto trigger_cleanup;
    }
  }

  if (tr_tm == TK_INSTEAD){
    tr_tm = TK_BEFORE;
  }

  /* Build the Trigger object */
  nt = (Trigger*)sqliteMalloc(sizeof(Trigger));
  if( nt==0 ) goto trigger_cleanup;
  nt->name = sqliteStrNDup(pName->z, pName->n);
  nt->table = sqliteStrNDup(pTableName->z, pTableName->n);

# trig-1.2: Error if trigger already exists
# trig-1.3: Created triggers are deleted if the transaction is rolled back
# trig-1.4: DROP TRIGGER removes trigger
# trig-1.5: Dropped triggers are restored if the transaction is rolled back
# trig-1.6: Error if dropped trigger doesn't exist
# trig-1.7: Dropping the table automatically drops all triggers
# trig-1.8: A trigger created on a TEMP table is not inserted into sqlite_master
# trig-1.9: Ensure that we cannot create a trigger on sqlite_master
# trig-1.10:
# trig-1.11:
# trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test trig_cd-1.1 {
   catchsql {

      delete from t1 WHERE a=old.a+2;
    end;
    update t1 set b='x-' || b where a in (1,3);
    select * from t1;
    drop table t1;
  }
} {1 x-a 2 b 4 d}

# Ensure that we cannot create INSTEAD OF triggers on tables
do_test trig_cd-1.12 {
  catchsql {
    create table t1(a,b);
    create trigger t1t instead of update on t1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create INSTEAD OF trigger on table: t1}}
# Ensure that we cannot create BEFORE triggers on views
do_test trig_cd-1.13 {
  catchsql {
    create view v1 as select * from t1;
    create trigger v1t before update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create BEFORE trigger on view: v1}}
# Ensure that we cannot create AFTER triggers on views
do_test trig_cd-1.14 {
  catchsql {
    create table t1(a,b);
    create view v1 as select * from t1;
    create trigger v1t AFTER update on v1 for each row begin
      delete from t1 WHERE a=old.a+2;
    end;
  }
} {1 {cannot create AFTER trigger on view: v1}}

finish_test

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.36 2002/05/26 23:24:41 danielk1977 Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>

Section {CREATE TRIGGER} createtrigger

Syntax {sql-statement} {
CREATE TRIGGER <trigger-name> [ BEFORE | AFTER ]
<database-event> ON <table-name>
<trigger-action>
}

Syntax {sql-statement} {
CREATE TRIGGER <trigger-name> INSTEAD OF
<database-event> ON <view-name>
<trigger-action>
}

Syntax {database-event} {
DELETE | 
INSERT | 
UPDATE | 
UPDATE OF <column-list>
}

<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
the statement causing the trigger to fire, then this conflict handling
policy is used instead.</p>

<p>Triggers are automatically dropped when the table that they are 
associated with is dropped.</p>

<p>Triggers may be created on views, as well as ordinary tables, by specifying
INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE
or ON UPDATE triggers are defined on a view, then it is not an error to execute
an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter,
executing an INSERT, DELETE or UPDATE on the view causes the associated
  triggers to fire. The real tables underlying the view are not modified
  (except possibly explicitly, by a trigger program).</p>

<p><b>Example:</b></p>

<p>Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>