当前位置: 代码迷 >> 其他数据库 >> ,写一个C语言sqlite3的触发器小例子
  详细解决方案

,写一个C语言sqlite3的触发器小例子

热度:2350   发布时间:2013-02-26 00:00:00.0
求助,写一个C语言sqlite3的触发器小例子
情况是这样的,由于需要,创建2个表,当一个表中的数据发生改变时,另一个表中的数据也同时改变,用C实现。
尽量详细一些,万分感谢。。

------解决方案--------------------------------------------------------
http://wenku.baidu.com/view/d218d8eeaeaad1f346933fce
------解决方案--------------------------------------------------------
SQLITE中并不需要用C来写吧。你可以直接在SQITE中用SQLITE的SQL语句写简单触发器。

参考一下SQLITE的文档中的例子和说明。


引用
The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations that are automatically performed when a specified database event occurs.

A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table.

At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:

INSERT NEW references are valid
UPDATE NEW and OLD references are valid
DELETE OLD references are valid


If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.

The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row.

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped. However if the the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.

Triggers are removed using the DROP TRIGGER statement.

Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers
The UPDATE, DELETE, and INSERT statements within triggers do not support the full syntax for UPDATE, DELETE, and INSERT statements. The following restrictions apply:

?The name of the table to be modified in an UPDATE, DELETE, or INSERT statement must be an unqualified table name. In other words, one must use just "tablename" not "database.tablename" when specifying the table. The table to be modified must exist in the same database as the table or view to which the trigger is attached.

?The "INSERT INTO table DEFAULT VALUES" form of the INSERT statement is not supported.

?The INDEXED BY and NOT INDEXED clauses are not supported for UPDATE and DELETE statements.

?The ORDER BY and LIMIT clauses on UPDATE and DELETE statements are not supported. ORDER BY and LIMIT are not normally supported for UPDATE or DELETE in any context but can be enabled for top-level statements using the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option. However, that compile-time option only applies to top-level UPDATE and DELETE statements, not UPDATE and DELETE statements within triggers.

INSTEAD OF trigger