Как удалить триггер sqlite

Русские Блоги

Когда в базе данных слишком много данных, часто необходимо очистить и удалить некоторые устаревшие данные, но часто нет подходящего времени для очистки. Таким образом, SQLite предоставляет триггер, который может запускаться и обрабатываться при наступлении определенных событий.

Trigger

SQLite Trigger (Trigger) — это функция обратного вызова базы данных, она будет автоматически выполняться / вызываться при возникновении указанного события базы данных. Ниже приведены некоторые примечания:

• Можно указать триггеры SQLite для срабатывания, когда DELETE, INSERT или UPDATE происходит в определенной таблице базы данных или когда обновляется один или несколько столбцов указанной таблицы.
• Предложения WHEN и действия триггера (триггера) могут обращаться к вставленным, удаленным или обновленным элементам строки с использованием ссылок в формах NEW.column-name и OLD.column-name, где имя-столбца — это столбец из таблицы, связанной с триггером. имя
• Если предусмотрено предложение WHEN, оператор SQL выполняется только для строк, заданных предложением WHEN как истинное. Если предложение WHEN не указано, оператор SQL выполняется для всех строк.
• Ключевое слово BEFORE или AFTER определяет, когда выполнять действие триггера, и решение состоит в том, чтобы выполнить действие триггера до или после вставки, изменения или удаления связанной строки.
• Когда таблица, связанная с триггером, удаляется, триггер автоматически удаляется (Триггер)
• Изменяемая таблица должна существовать в той же базе данных, что и таблица или представление, присоединенное в качестве триггера, и должна использоваться толькоtablenameВместоdatabase.tablename。

грамматика

Здесь event_name может быть операциями INSERT, DELETE и UPDATE базы данных в упомянутой таблице table_name.

Пример

Давайте предположим ситуацию, когда мы хотим поддерживать контрольный тест для каждой записи, вставленной во вновь созданную таблицу COMPANY (если она уже существует, удалите и создайте заново):

Чтобы продолжить аудит, мы создадим новую таблицу под названием AUDIT. Всякий раз, когда в таблице COMPANY появляется новая запись, в нее будет вставлено сообщение журнала:

Здесь ID — это ID записи AUDIT, EMP_ID — это ID из таблицы COMPANY, а DATE будет содержать метку времени, когда была создана запись в COMPANY. Итак, давайте теперь создадим триггер в таблице COMPANY следующим образом:

Теперь мы начнем вставлять записи в таблицу COMPANY, что приведет к созданию записи журнала аудита в таблице AUDIT. Поэтому давайте создадим запись в таблице COMPANY следующим образом:

Это создаст следующую запись в таблице COMPANY:

При этом запись будет создана в таблице AUDIT. Эта запись является результатом триггера, который мы создали для операции INSERT в таблице COMPANY. Точно так же при необходимости могут быть созданы триггеры для операций UPDATE и DELETE.

Список триггеров (TRIGGERS)

Вы можете перечислить все триггеры из таблицы sqlite_master следующим образом:

В приведенном выше операторе SQLite будет перечислена только одна запись, а именно:

Если вы хотите перечислить триггеры в определенной таблице, используйте предложение AND, чтобы объединить имена таблиц следующим образом:

В приведенном выше операторе SQLite будет перечислена только одна запись, а именно:

Удалить триггер (TRIGGERS)

Ниже приводится команда DROP, которую можно использовать для удаления существующего триггера:

SQLite Trigger

Summary: this tutorial discusses SQLite trigger, which is a database object fired automatically when the data in a table is changed.

What is an SQLite trigger

An SQLite trigger is a named database object that is executed automatically when an INSERT , UPDATE or DELETE statement is issued against the associated table.

When do we need SQLite triggers

You often use triggers to enable sophisticated auditing. For example, you want to log the changes in the sensitive data such as salary and address whenever it changes.

In addition, you use triggers to enforce complex business rules centrally at the database level and prevent invalid transactions.

SQLite CREATE TRIGGER statement

To create a new trigger in SQLite, you use the CREATE TRIGGER statement as follows:

• First, specify the name of the trigger after the CREATE TRIGGER keywords.
• Next, determine when the trigger is fired such as BEFORE , AFTER , or INSTEAD OF . You can create BEFORE and AFTER triggers on a table. However, you can only create an INSTEAD OF trigger on a view.
• Then, specify the event that causes the trigger to be invoked such as INSERT , UPDATE , or DELETE .
• After that, indicate the table to which the trigger belongs.
• Finally, place the trigger logic in the BEGIN END block, which can be any valid SQL statements.

If you combine the time when the trigger is fired and the event that causes the trigger to be fired, you have a total of 9 possibilities:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE
• INSTEAD OF INSERT
• INSTEAD OF DELETE
• INSTEAD OF UPDATE

Suppose you use a UPDATE statement to update 10 rows in a table, the trigger that associated with the table is fired 10 times. This trigger is called FOR EACH ROW trigger. If the trigger associated with the table is fired one time, we call this trigger a FOR EACH STATEMENT trigger.

As of version 3.9.2, SQLite only supports FOR EACH ROW triggers. It has not yet supported the FOR EACH STATEMENT triggers.

If you use a condition in the WHEN clause, the trigger is only invoked when the condition is true. In case you omit the WHEN clause, the trigger is executed for all rows.

Notice that if you drop a table, all associated triggers are also deleted. However, if the trigger references other tables, the trigger is not removed or changed if other tables are removed or updated.

For example, a trigger references to a table named people , you drop the people table or rename it, you need to manually change the definition of the trigger.

You can access the data of the row being inserted, deleted, or updated using the OLD and NEW references in the form: OLD.column_name and NEW.column_name .

the OLD and NEW references are available depending on the event that causes the trigger to be fired.

The following table illustrates the rules.:

Action	Reference
INSERT	NEW is available
UPDATE	Both NEW and OLD are available
DELETE	OLD is available

SQLite triggers examples

Let’s create a new table called leads to store all business leads of the company.

1) SQLite BEFORE INSERT trigger example

Suppose you want to validate the email address before inserting a new lead into the leads table. In this case, you can use a BEFORE INSERT trigger.

First, create a BEFORE INSERT trigger as follows:

We used the NEW reference to access the email column of the row that is being inserted.

To validate the email, we used the LIKE operator to determine whether the email is valid or not based on the email pattern. If the email is not valid, the RAISE function aborts the insert and issues an error message.

Second, insert a row with an invalid email into the leads table.

SQLite issued an error: “Invalid email address” and aborted the execution of the insert.

Third, insert a row with a valid email.

Because the email is valid, the insert statement executed successfully.

2) SQLite AFTER UPDATE trigger example

The phones and emails of the leads are so important that you can’t afford to lose this information. For example, someone accidentally updates the email or phone to the wrong ones or even delete it.

To protect this valuable data, you use a trigger to log all changes which are made to the phone and email.

First, create a new table called lead_logs to store the historical data.

Second, create an AFTER UPDATE trigger to log data to the lead_logs table whenever there is an update in the email or phone column.

You notice that in the condition in the WHEN clause specifies that the trigger is invoked only when there is a change in either email or phone column.

Third, update the last name of John from Doe to Smith .

The trigger log_contact_after_update was not invoked because there was no change in email or phone.

Fourth, update both email and phone of John to the new ones.

If you check the log table, you will see there is a new entry there.

You can develop the AFTER INSERT and AFTER DELETE triggers to log the data in the lead_logs table as an excercise.

SQLite DROP TRIGGER statement

To drop an existing trigger, you use the DROP TRIGGER statement as follows:

• First, specify the name of the trigger that you want to drop after the DROP TRIGGER keywords.
• Second, use the IF EXISTS option to delete the trigger only if it exists.

Note that if you drop a table, SQLite will automatically drop all triggers associated with the table.

For example, to remove the validate_email_before_insert_leads trigger, you use the following statement:

In this tutorial, we have introduced you to SQLite triggers and show you how to create and drop triggers from the database.

SQL As Understood By 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 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 triggers

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. Instead, 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).

Note that the sqlite3_changes() and sqlite3_total_changes() interfaces do not count INSTEAD OF trigger firings, but the count_changes pragma does count INSTEAD OF trigger firing.

Some Example Triggers

Assuming that customer records are stored in the «customers» table, and that order records are stored in the «orders» table, the following UPDATE trigger ensures that all associated orders are redirected when a customer changes his or her address:

With this trigger installed, executing the statement:

causes the following to be automatically executed:

For an example of an INSTEAD OF trigger, consider the following schema:

With the schema above, a statement of the form:

Causes the customer.cust_addr field to be updated for a specific customer entry that has customer.cust_id equal to the $cust_id parameter. Note how the values assigned to the view are made available as field in the special «NEW» table within the trigger body.

Cautions On The Use Of BEFORE triggers

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer.

Because of the behaviors described above, programmers are encouraged to prefer AFTER triggers over BEFORE triggers.

The RAISE() function

A special SQL function RAISE() may be used within a trigger-program, with the following syntax

When one of RAISE(ROLLBACK. ), RAISE(ABORT. ) or RAISE(FAIL. ) is called during trigger-program execution, the specified ON CONFLICT processing is performed the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the application, along with the specified error message.

When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would have been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step.

TEMP Triggers on Non-TEMP Tables

A trigger normally exists in the same database as the table named after the «ON» keyword in the CREATE TRIGGER statement. Except, it is possible to create a TEMP TRIGGER on a table in another database. Such a trigger will only fire when changes are made to the target table by the application that defined the trigger. Other applications that modify the database will not be able to see the TEMP trigger and hence cannot run the trigger.

When defining a TEMP trigger on a non-TEMP table, it is important to specify the database holding the non-TEMP table. For example, in the following statement, it is important to say «main.tab1» instead of just «tab1»:

Failure to specify the database name on the target table could result in the TEMP trigger being reattached to a table with the same name in another database whenever any schema change occurs.

SQLite Triggers

A trigger is an event-driven action that is run automatically when a specified change operation ( INSERT, UPDATE, and DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

Table of contents

Uses for triggers:

• Enforce business rules
• Validate input data
• Generate a unique value for a newly-inserted row in a different file.
• Write to other files for audit trail purposes
• Query from other files for cross-referencing purposes
• Access system functions
• Replicate data to different files to achieve data consistency

Benefits of using triggers in business:

• Faster application development. Because the database stores triggers, you do not have to code the trigger actions into each database application.
• Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
• Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
• Improve performance in client/server environment. All rules run on the server before the result returns.

Implementation of SQL triggers is based on the SQL standard. It supports constructs that are common to most programming languages. It supports the declaration of local variables, statements to control the flow of the procedure, assignment of expression results to variables, and error handling.

SQLite: Create trigger

A trigger is a named database object that is associated with a table, and it activates when a particular event (e.g. an insert, update or delete) occurs for the table/views. The statement CREATE TRIGGER creates a new trigger in 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.

Here is the syntax :

Syntax:

Parameters:

Name	Description
trigger-name	The name of the trigger. A trigger must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table.
BEFORE AFTER INSTEAD OF	Determines whether the function is called before, after, or instead of the event. A constraint trigger can only be specified as AFTER.
database-event	One of the INSERT, UPDATE, DELETE that will fire the trigger.
table-name	The name of the table or view the trigger is for.
FOR EACH ROW FOR EACH STATEMENT	Specifies whether the trigger procedure should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default.
expression	A Boolean expression that determines whether the trigger function will actually be executed.
trigger-step	Action for the trigger, it is the sql statement.

There is two SQLite extension to triggers ‘OLD‘ and ‘NEW‘. OLD and NEW are not case sensitive.

• Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger
• In an INSERT trigger, only NEW.col_name can be used.
• In a UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
• In a DELETE trigger, only OLD.col_name can be used; there is no new row.

Sample database, table, table structure, table records for various examples

emp_details

SQLite Trigger: Example AFTER INSERT

In the following example, we have two tables : emp_details and emp_log. To insert some information into emp_logs table (which have three fields emp_id and salary and edttime) every time, when an INSERT happen into emp_details table we have used the following trigger :

Here is the trigger ins_same_rec:

Records of the table (on some columns): emp_details

Records of the table (all columns): emp_log

Now insert one record in emp_details table see the records both in emp_details and emp_log tables :

itemscope itemtype=»http://schema.org/WebPageElement/Heading»>SQLite Trigger : Example BEFORE INSERT

In the following example, before inserting a new record in emp_details table, a trigger check the column value of FIRST_NAME, LAST_NAME, JOB_ID and
— If there are any space(s) before or after the FIRST_NAME, LAST_NAME, LTRIM() function will remove those.
— The value of the JOB_ID will be converted to upper cases by UPPER() function.

Here is the trigger befo_insert:

Now insert a row into emp_details table (check the employee_id column, whether it is exists or not.) :

Now, here is the output.

SQLite Trigger: Example AFTER UPDATE

We have two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS. stu_log table has two columns user_id and description.

Let we promote all the students in next class i.e. 7 will be 8, 8 will be 9 and so on. After updating a single row in student_mast table a new row will be inserted in stu_log table where we will store the current user id and a small description regarding the current update. Here is the trigger code :

Here is the trigger for that event-

Now update the student_mast table:

The trigger shows you the updated records in ‘stu_log’. Here is the latest position of student_mast and stu_log tables :

SQLite Trigger: Example BEFORE UPDATE

We have two tables student_mast and student_marks. Here are the sample tables below. The student_id column of student_mast table is the primary key and in student_marks table, it is a foreign key, the reference to student_id column of student_mast table.

Here is the trigger

Now we are going to update the primary key column of student_mast table and look the result below.

SQLite Trigger: Example AFTER DELETE

In our ‘AFTER UPDATE’ example, we had two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS and stu_log table has two columns user_id and description. We want to store some information in stu_log table after a delete operation happened on student_mast table. Here is the trigger :

Here is the trigger

Let delete a student from student_mast

Here is the latest position of student_mast, stu_log tables :

SQLite Trigger: Example BEFORE DELETE

We have two tables student_mast and student_marks. Here are the sample tables below. The student_id column of student_mast table is the primary key and in student_marks table, it is a foreign key, a reference to student_id column of student_mast table.

Here is the trigger

Let try to delete a student from student_marks and see the result.

SQLite trigger using INSTEAD OF

Here is the sample table emp_details.

Now create a view name emp_details_view.

Now see the just created view .

Here is the view.

INSERT TRIGGER using INSTEAD OF

Here is the example

Now insert the rows in the emp_details_view and the triggers will propagate those changes to the underlying table..

Now look the view and the base table

UPDATE TRIGGER using INSTEAD OF

Here is the example

Now update the rows in the emp_details_view.

Now look the view and the base table

DELETE TRIGGER using INSTEAD OF

Here is the example

Now delete the row from the emp_details_view which employee_id is 106, and look the result.

Now look the view and the base table

The row has been deleted which contain the employee_id 106.

DROP an SQLite trigger

To delete or destroy a trigger, use a DROP TRIGGER statement. To execute this command, the current user must be the owner of the table for which the trigger is defined.

Syntax:

Example:

If you delete or drop the just created trigger delete_stu the following statement can be used: