# The author disclaims copyright to this source code.  In place of

# a legal notice, here is a blessing:

#

#    May you do good and not evil.

#    May you find forgiveness for yourself and forgive others.

#    May you share freely, never taking more than you give.

#

#***********************************************************************

#

# This file tests creating and dropping triggers, and interaction thereof

# with the database COMMIT/ROLLBACK logic.

#

# 1. CREATE and DROP TRIGGER tests

# trig-1.1: Error if table does not exist

# 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

ifcapable {!trigger} {

  finish_test

  return

}

do_test trigger1-1.1.1 {

   catchsql {

     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN

       SELECT * from sqlite_master;

     END;

   } 

} {1 {no such table: main.no_such_table}}

ifcapable tempdb {

  do_test trigger1-1.1.2 {

     catchsql {

       CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN

         SELECT * from sqlite_master;

       END;

     } 

  } {1 {no such table: no_such_table}}

}

execsql {

    CREATE TABLE t1(a);

}

do_test trigger1-1.1.3 {

  catchsql {

     CREATE TRIGGER trig UPDATE ON t1 FOR EACH STATEMENT BEGIN

        SELECT * FROM sqlite_master;

     END;

  }

} {1 {near "STATEMENT": syntax error}}

execsql {

	CREATE TRIGGER tr1 INSERT ON t1 BEGIN

	  INSERT INTO t1 values(1);

 	END;

}

do_test trigger1-1.2.0 {

    catchsql {

	CREATE TRIGGER IF NOT EXISTS tr1 DELETE ON t1 BEGIN

	    SELECT * FROM sqlite_master;

 	END

     }

} {0 {}}

do_test trigger1-1.2.1 {

    catchsql {

	CREATE TRIGGER tr1 DELETE ON t1 BEGIN

	    SELECT * FROM sqlite_master;

 	END

     }

} {1 {trigger tr1 already exists}}

do_test trigger1-1.2.2 {

    catchsql {

	CREATE TRIGGER "tr1" DELETE ON t1 BEGIN

	    SELECT * FROM sqlite_master;

 	END

     }

} {1 {trigger "tr1" already exists}}

do_test trigger1-1.2.3 {

    catchsql {

	CREATE TRIGGER [tr1] DELETE ON t1 BEGIN

	    SELECT * FROM sqlite_master;

 	END

     }

} {1 {trigger [tr1] already exists}}

do_test trigger1-1.3 {

    catchsql {

	BEGIN;

	CREATE TRIGGER tr2 INSERT ON t1 BEGIN

	    SELECT * from sqlite_master; END;

        ROLLBACK;

	CREATE TRIGGER tr2 INSERT ON t1 BEGIN

	    SELECT * from sqlite_master; END;

    }

} {0 {}}

do_test trigger1-1.4 {

    catchsql {

	DROP TRIGGER IF EXISTS tr1;

	CREATE TRIGGER tr1 DELETE ON t1 BEGIN

	    SELECT * FROM sqlite_master;

	END

    }

} {0 {}}

do_test trigger1-1.5 {

    execsql {

	BEGIN;

	DROP TRIGGER tr2;

	ROLLBACK;

	DROP TRIGGER tr2;

    }

} {}

do_test trigger1-1.6.1 {

    catchsql {

	DROP TRIGGER IF EXISTS biggles;

    }

} {0 {}}

do_test trigger1-1.6.2 {

    catchsql {

	DROP TRIGGER biggles;

    }

} {1 {no such trigger: biggles}}

do_test trigger1-1.7 {

    catchsql {

	DROP TABLE t1;

	DROP TRIGGER tr1;

    }

} {1 {no such trigger: tr1}}

ifcapable tempdb {

  execsql {

    CREATE TEMP TABLE temp_table(a);

  }

  do_test trigger1-1.8 {

    execsql {

  	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN

  	    SELECT * from sqlite_master;

  	END;

  	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';

    } 

  } {0}

}

do_test trigger1-1.9 {

  catchsql {

    CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN

       SELECT * FROM sqlite_master;

    END;

  }

} {1 {cannot create trigger on system table}}

# Check to make sure that a DELETE statement within the body of

# a trigger does not mess up the DELETE that caused the trigger to

# run in the first place.

#

do_test trigger1-1.10 {

  execsql {

    create table t1(a,b);

    insert into t1 values(1,'a');

    insert into t1 values(2,'b');

    insert into t1 values(3,'c');

    insert into t1 values(4,'d');

    create trigger r1 after delete on t1 for each row begin

      delete from t1 WHERE a=old.a+2;

    end;

    delete from t1 where a=1 OR a=3;

    select * from t1;

    drop table t1;

  }

} {2 b 4 d}

do_test trigger1-1.11 {

  execsql {

    create table t1(a,b);

    insert into t1 values(1,'a');

    insert into t1 values(2,'b');

    insert into t1 values(3,'c');

    insert into t1 values(4,'d');

    create trigger r1 after update on t1 for each row begin

      delete from t1 WHERE a=old.a+2;

    end;

    update t1 set b='x-' || b where a=1 OR a=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 trigger1-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: main.t1}}

ifcapable view {

# Ensure that we cannot create BEFORE triggers on views

do_test trigger1-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: main.v1}}

# Ensure that we cannot create AFTER triggers on views

do_test trigger1-1.14 {

  catchsql {

    drop view v1;

    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: main.v1}}

} ;# ifcapable view

# Check for memory leaks in the trigger parser

#

do_test trigger1-2.1 {

  catchsql {

    CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

      SELECT * FROM;  -- Syntax error

    END;

  }

} {1 {near ";": syntax error}}

do_test trigger1-2.2 {

  catchsql {

    CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

      SELECT * FROM t1;

      SELECT * FROM;  -- Syntax error

    END;

  }

} {1 {near ";": syntax error}}

# Create a trigger that refers to a table that might not exist.

#

ifcapable tempdb {

  do_test trigger1-3.1 {

    execsql {

      CREATE TEMP TABLE t2(x,y);

    }

    catchsql {

      CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN

        INSERT INTO t2 VALUES(NEW.a,NEW.b);

      END;

    }

  } {0 {}}

  do_test trigger-3.2 {

    catchsql {

      INSERT INTO t1 VALUES(1,2);

      SELECT * FROM t2;

    }

  } {1 {no such table: main.t2}}

  do_test trigger-3.3 {

    db close

    set rc [catch {sqlite3 db test.db} err]

    if {$rc} {lappend rc $err}

    set rc

  } {0}

  do_test trigger-3.4 {

    catchsql {

      INSERT INTO t1 VALUES(1,2);

      SELECT * FROM t2;

    }

  } {1 {no such table: main.t2}}

  do_test trigger-3.5 {

    catchsql {

      CREATE TEMP TABLE t2(x,y);

      INSERT INTO t1 VALUES(1,2);

      SELECT * FROM t2;

    }

  } {1 {no such table: main.t2}}

  do_test trigger-3.6 {

    catchsql {

      DROP TRIGGER r1;

      CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN

        INSERT INTO t2 VALUES(NEW.a,NEW.b);

      END;

      INSERT INTO t1 VALUES(1,2);

      SELECT * FROM t2;

    }

  } {0 {1 2}}

  do_test trigger-3.7 {

    execsql {

      DROP TABLE t2;

      CREATE TABLE t2(x,y);

      SELECT * FROM t2;

    }

  } {}

  # There are two versions of trigger-3.8 and trigger-3.9. One that uses

  # compound SELECT statements, and another that does not.

  ifcapable compound {

  do_test trigger1-3.8 {

    execsql {

      INSERT INTO t1 VALUES(3,4);

      SELECT * FROM t1 UNION ALL SELECT * FROM t2;

    }

  } {1 2 3 4 3 4}

  do_test trigger1-3.9 {

    db close

    sqlite3 db test.db

    execsql {

      INSERT INTO t1 VALUES(5,6);

      SELECT * FROM t1 UNION ALL SELECT * FROM t2;

    }

  } {1 2 3 4 5 6 3 4}

  } ;# ifcapable compound

  ifcapable !compound {

  do_test trigger1-3.8 {

    execsql {

      INSERT INTO t1 VALUES(3,4);

      SELECT * FROM t1; 

      SELECT * FROM t2;

    }

  } {1 2 3 4 3 4}

  do_test trigger1-3.9 {

    db close

    sqlite3 db test.db

    execsql {

      INSERT INTO t1 VALUES(5,6);

      SELECT * FROM t1;

      SELECT * FROM t2;

    }

  } {1 2 3 4 5 6 3 4}

  } ;# ifcapable !compound

  do_test trigger1-4.1 {

    execsql {

      CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN

        INSERT INTO t2 VALUES(NEW.a,NEW.b);

      END;

      INSERT INTO t1 VALUES(7,8);

      SELECT * FROM t2;

    }

  } {3 4 7 8}

  do_test trigger1-4.2 {

    sqlite3 db2 test.db

    execsql {

      INSERT INTO t1 VALUES(9,10);

    } db2;

    db2 close

    execsql {

      SELECT * FROM t2;

    }

  } {3 4 7 8}

  do_test trigger1-4.3 {

    execsql {

      DROP TABLE t1;

      SELECT * FROM t2;

    };

  } {3 4 7 8}

  do_test trigger1-4.4 {

    db close

    sqlite3 db test.db

    execsql {

      SELECT * FROM t2;

    };

  } {3 4 7 8}

} else {

  execsql {

    CREATE TABLE t2(x,y);

    DROP TABLE t1;

    INSERT INTO t2 VALUES(3, 4);

    INSERT INTO t2 VALUES(7, 8);

  }

}

integrity_check trigger1-5.1

# Create a trigger with the same name as a table.  Make sure the

# trigger works.  Then drop the trigger.  Make sure the table is

# still there.

#

set view_v1 {}

ifcapable view {

  set view_v1 {view v1}

}

do_test trigger1-6.1 {

  execsql {SELECT type, name FROM sqlite_master}

} [concat $view_v1 {table t2}]

do_test trigger1-6.2 {

  execsql {

    CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN

      SELECT RAISE(ABORT,'deletes are not allows');

    END;

    SELECT type, name FROM sqlite_master;

  }

} [concat $view_v1 {table t2 trigger t2}]

do_test trigger1-6.3 {

  catchsql {DELETE FROM t2}

} {1 {deletes are not allows}}

do_test trigger1-6.4 {

  execsql {SELECT * FROM t2}

} {3 4 7 8}

do_test trigger1-6.5 {

  db close

  sqlite3 db test.db

  execsql {SELECT type, name FROM sqlite_master}

} [concat $view_v1 {table t2 trigger t2}]

do_test trigger1-6.6 {

  execsql {

    DROP TRIGGER t2;

    SELECT type, name FROM sqlite_master;

  }

} [concat $view_v1 {table t2}]

do_test trigger1-6.7 {

  execsql {SELECT * FROM t2}

} {3 4 7 8}

do_test trigger1-6.8 {

  db close

  sqlite3 db test.db

  execsql {SELECT * FROM t2}

} {3 4 7 8}

integrity_check trigger-7.1

# Check to make sure the name of a trigger can be quoted so that keywords

# can be used as trigger names.  Ticket #468

#

do_test trigger1-8.1 {

  execsql {

    CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;

    SELECT name FROM sqlite_master WHERE type='trigger';

  }

} {trigger}

do_test trigger1-8.2 {

  execsql {

    DROP TRIGGER 'trigger';

    SELECT name FROM sqlite_master WHERE type='trigger';

  }

} {}

do_test trigger1-8.3 {

  execsql {

    CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;

    SELECT name FROM sqlite_master WHERE type='trigger';

  }

} {trigger}

do_test trigger1-8.4 {

  execsql {

    DROP TRIGGER "trigger";

    SELECT name FROM sqlite_master WHERE type='trigger';

  }

} {}

do_test trigger1-8.5 {

  execsql {

    CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;

    SELECT name FROM sqlite_master WHERE type='trigger';

  }

} {trigger}

do_test trigger1-8.6 {

  execsql {

    DROP TRIGGER [trigger];

    SELECT name FROM sqlite_master WHERE type='trigger';

  }

} {}

ifcapable conflict {

  # Make sure REPLACE works inside of triggers.

  #

  # There are two versions of trigger-9.1 and trigger-9.2. One that uses

  # compound SELECT statements, and another that does not.

  ifcapable compound {

    do_test trigger1-9.1 {

      execsql {

        CREATE TABLE t3(a,b);

        CREATE TABLE t4(x UNIQUE, b);

        CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN

          REPLACE INTO t4 VALUES(new.a,new.b);

        END;

        INSERT INTO t3 VALUES(1,2);

        SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;

      }

    } {1 2 99 99 1 2}

    do_test trigger1-9.2 {

      execsql {

        INSERT INTO t3 VALUES(1,3);

        SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;

      }

    } {1 2 1 3 99 99 1 3}

  } else {

    do_test trigger1-9.1 {

      execsql {

        CREATE TABLE t3(a,b);

        CREATE TABLE t4(x UNIQUE, b);

        CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN

          REPLACE INTO t4 VALUES(new.a,new.b);

        END;

        INSERT INTO t3 VALUES(1,2);

        SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;

      }

    } {1 2 99 99 1 2}

    do_test trigger1-9.2 {

      execsql {

        INSERT INTO t3 VALUES(1,3);

        SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;

      }

    } {1 2 1 3 99 99 1 3}

  }

  execsql {

    DROP TABLE t3;

    DROP TABLE t4;

  }

}

# Ticket #764. At one stage TEMP triggers would fail to re-install when the

# schema was reloaded. The following tests ensure that TEMP triggers are

# correctly re-installed.

#

# Also verify that references within trigger programs are resolved at

# statement compile time, not trigger installation time. This means, for

# example, that you can drop and re-create tables referenced by triggers. 

ifcapable tempdb&&attach {

  do_test trigger1-10.0 {

    file delete -force test2.db

    file delete -force test2.db-journal

    execsql {

      ATTACH 'test2.db' AS aux;

    }

  } {}

  do_test trigger1-10.1 {

    execsql {

      CREATE TABLE main.t4(a, b, c);

      CREATE TABLE temp.t4(a, b, c);

      CREATE TABLE aux.t4(a, b, c);

      CREATE TABLE insert_log(db, a, b, c);

    }

  } {}

  do_test trigger1-10.2 {

    execsql {

      CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN 

        INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);

      END;

      CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN 

        INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);

      END;

      CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN 

        INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);

      END;

    }

  } {}

  do_test trigger1-10.3 {

    execsql {

      INSERT INTO main.t4 VALUES(1, 2, 3);

      INSERT INTO temp.t4 VALUES(4, 5, 6);

      INSERT INTO aux.t4  VALUES(7, 8, 9);

    }

  } {}

  do_test trigger1-10.4 {

    execsql {

      SELECT * FROM insert_log;

    }

  } {main 1 2 3 temp 4 5 6 aux 7 8 9}

  do_test trigger1-10.5 {

    execsql {

      BEGIN;

      INSERT INTO main.t4 VALUES(1, 2, 3);

      INSERT INTO temp.t4 VALUES(4, 5, 6);

      INSERT INTO aux.t4  VALUES(7, 8, 9);

      ROLLBACK;

    }

  } {}

  do_test trigger1-10.6 {

    execsql {

      SELECT * FROM insert_log;

    }

  } {main 1 2 3 temp 4 5 6 aux 7 8 9}

  do_test trigger1-10.7 {

    execsql {

      DELETE FROM insert_log;

      INSERT INTO main.t4 VALUES(11, 12, 13);

      INSERT INTO temp.t4 VALUES(14, 15, 16);

      INSERT INTO aux.t4  VALUES(17, 18, 19);

    }

  } {}

  do_test trigger1-10.8 {

    execsql {

      SELECT * FROM insert_log;

    }

  } {main 11 12 13 temp 14 15 16 aux 17 18 19}

  do_test trigger1-10.8 {

  # Drop and re-create the insert_log table in a different database. Note

  # that we can change the column names because the trigger programs don't

  # use them explicitly.

    execsql {

      DROP TABLE insert_log;

      CREATE TABLE aux.insert_log(db, d, e, f);

    }

  } {}

  do_test trigger1-10.10 {

    execsql {

      INSERT INTO main.t4 VALUES(21, 22, 23);

      INSERT INTO temp.t4 VALUES(24, 25, 26);

      INSERT INTO aux.t4  VALUES(27, 28, 29);

    }

  } {}

  do_test trigger1-10.11 {

    execsql {

      SELECT * FROM insert_log;

    }

  } {main 21 22 23 temp 24 25 26 aux 27 28 29}

}

do_test trigger1-11.1 {

  catchsql {SELECT raise(abort,'message');}

} {1 {RAISE() may only be used within a trigger-program}}

finish_test