{"id":664,"date":"2015-12-21T15:17:24","date_gmt":"2015-12-21T08:17:24","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=664"},"modified":"2020-04-11T09:41:55","modified_gmt":"2020-04-11T02:41:55","slug":"sqlite-trigger","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/","title":{"rendered":"SQLite Trigger"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: this tutorial discusses SQLite trigger, which is a database object fired automatically when the data in a table is changed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What is an SQLite trigger<\/h2>\n\n\n\n<p>An SQLite trigger is a named database object that is executed automatically when an <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-insert\/\">INSERT<\/a><\/code>, <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-update\/\"><code>UPDATE<\/code><\/a>&nbsp;or <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-delete\/\">DELETE<\/a><\/code>&nbsp;statement is issued against the associated table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">When do we&nbsp;need SQLite triggers<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In addition, you&nbsp;use triggers to enforce complex business rules centrally at the database level and prevent invalid transactions.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">SQLite CREATE TRIGGER statement<\/h2>\n\n\n\n<p>To create a new trigger in SQLite, you use the <code>CREATE TRIGGER<\/code> statement as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> &#91;<span class=\"hljs-keyword\">IF<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">EXISTS<\/span>] trigger_name \n   &#91;<span class=\"hljs-keyword\">BEFORE<\/span>|<span class=\"hljs-keyword\">AFTER<\/span>|INSTEAD <span class=\"hljs-keyword\">OF<\/span>] &#91;<span class=\"hljs-keyword\">INSERT<\/span>|<span class=\"hljs-keyword\">UPDATE<\/span>|<span class=\"hljs-keyword\">DELETE<\/span>] \n   <span class=\"hljs-keyword\">ON<\/span> table_name\n   &#91;<span class=\"hljs-keyword\">WHEN<\/span> condition]\n<span class=\"hljs-keyword\">BEGIN<\/span>\n statements;\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>First, &nbsp;specify the name of the trigger after the <code>CREATE TRIGGER<\/code> keywords.<\/li><li>Next, determine when the trigger is fired such as <code>BEFORE<\/code>, <code>AFTER<\/code>, or <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-instead-of-triggers\/\">INSTEAD OF<\/a><\/code>. You can create <code>BEFORE<\/code> and <code>AFTER<\/code> triggers on a table. However, you can only create an <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-instead-of-triggers\/\">INSTEAD OF<\/a><\/code> trigger on a view.<\/li><li>Then, specify the event that causes the trigger to be invoked such as <code>INSERT<\/code>, <code>UPDATE<\/code>, or <code>DELETE<\/code>.<\/li><li>After that, indicate the table to which the trigger belongs.<\/li><li>Finally, place the trigger logic in the <code>BEGIN END<\/code> block, which can be any valid SQL statements.<\/li><\/ul>\n\n\n\n<p>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:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>BEFORE INSERT<\/code><\/li><li><code>AFTER INSERT<\/code><\/li><li><code>BEFORE UPDATE<\/code><\/li><li><code>AFTER UPDATE<\/code><\/li><li><code>BEFORE DELETE<\/code><\/li><li><code>AFTER DELETE<\/code><\/li><li><code>INSTEAD OF INSERT<\/code><\/li><li><code>INSTEAD OF DELETE<\/code><\/li><li><code>INSTEAD OF UPDATE<\/code><\/li><\/ul>\n\n\n\n<p>Suppose&nbsp;you use a <code>UPDATE<\/code> statement to update 10 rows in a table, the trigger that associated with the table is fired 10 times. This trigger is called <code>FOR EACH ROW<\/code> trigger. If the trigger associated with the table is fired one time, we call this trigger a <code>FOR EACH STATEMENT<\/code> trigger.<\/p>\n\n\n\n<p>As of version 3.9.2, SQLite only supports <code>FOR EACH ROW<\/code> triggers. It has not yet supported the <code>FOR EACH STATEMENT<\/code> triggers.<\/p>\n\n\n\n<p>If you use&nbsp;a condition in the <code>WHEN<\/code> clause, the trigger is only invoked when the condition is true. In case you omit the <code>WHEN<\/code> clause, the trigger is executed for all rows.<\/p>\n\n\n\n<p>Notice that if you <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-drop-table\/\">drop a table<\/a>, 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.<\/p>\n\n\n\n<p>For example, a&nbsp;trigger references to a table&nbsp;named <code>people<\/code>, you drop the <code>people<\/code> table or rename it, you need to manually change the definition&nbsp;of the trigger.<\/p>\n\n\n\n<p>You can access the data of the row being inserted, deleted, or updated using the <code>OLD<\/code> and <code>NEW<\/code> references in the form: <code>OLD.column_name<\/code> and <code>NEW.column_name<\/code>.<\/p>\n\n\n\n<p>the <code>OLD<\/code> and <code>NEW<\/code> references are available depending on the event that causes the trigger to be fired.<\/p>\n\n\n\n<p>The following table illustrates the rules.:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"\"><thead><tr><th>Action<\/th><th>Reference<\/th><\/tr><\/thead><tbody><tr><td>INSERT<\/td><td>NEW is available<\/td><\/tr><tr><td>UPDATE<\/td><td>Both NEW and OLD&nbsp;are available<\/td><\/tr><tr><td>DELETE<\/td><td>OLD is available<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">SQLite triggers examples<\/h2>\n\n\n\n<p>Let&#8217;s <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-create-table\/\">create a new table<\/a> called leads to store all business leads of the company.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> leads (\n\t<span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">integer<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n\tfirst_name <span class=\"hljs-built_in\">text<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\tlast_name <span class=\"hljs-built_in\">text<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\tphone <span class=\"hljs-built_in\">text<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\temail <span class=\"hljs-built_in\">text<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n\t<span class=\"hljs-keyword\">source<\/span> <span class=\"hljs-built_in\">text<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\">1) SQLite BEFORE INSERT trigger example<\/h3>\n\n\n\n<p>Suppose you want to validate the email address before inserting a new lead into the <code>leads<\/code> table. In this case, you can use a <code>BEFORE INSERT<\/code> trigger.<\/p>\n\n\n\n<p>First, create a <code>BEFORE INSERT<\/code> trigger as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> validate_email_before_insert_leads \n   <span class=\"hljs-keyword\">BEFORE<\/span> <span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">ON<\/span> leads\n<span class=\"hljs-keyword\">BEGIN<\/span>\n   <span class=\"hljs-keyword\">SELECT<\/span>\n      <span class=\"hljs-keyword\">CASE<\/span>\n\t<span class=\"hljs-keyword\">WHEN<\/span> NEW.email <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">LIKE<\/span> <span class=\"hljs-string\">'%_@__%.__%'<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n   \t  <span class=\"hljs-keyword\">RAISE<\/span> (<span class=\"hljs-keyword\">ABORT<\/span>,<span class=\"hljs-string\">'Invalid email address'<\/span>)\n       <span class=\"hljs-keyword\">END<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>We used the <code>NEW<\/code> reference to access the email column of the row that is being inserted.<\/p>\n\n\n\n<p>To validate the email, we used the <code><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-like\/\">LIKE<\/a><\/code>&nbsp;operator to determine whether the email is valid or not based on the email pattern. If the email is not valid, the <code>RAISE<\/code> function aborts the insert and issues an error message.<\/p>\n\n\n\n<p>Second, insert a row with an invalid email into the <code>leads<\/code> table.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> leads (first_name,last_name,email,phone)\n<span class=\"hljs-keyword\">VALUES<\/span>(<span class=\"hljs-string\">'John'<\/span>,<span class=\"hljs-string\">'Doe'<\/span>,<span class=\"hljs-string\">'jjj'<\/span>,<span class=\"hljs-string\">'4089009334'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>SQLite issued an error: &#8220;Invalid email address&#8221; and aborted the execution of the insert.<\/p>\n\n\n\n<p>Third, insert a row with a valid email.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> leads (first_name, last_name, email, phone)\n<span class=\"hljs-keyword\">VALUES<\/span> (<span class=\"hljs-string\">'John'<\/span>, <span class=\"hljs-string\">'Doe'<\/span>, <span class=\"hljs-string\">'john.doe@sqlitetutorial.net'<\/span>, <span class=\"hljs-string\">'4089009334'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Because the email is valid, the insert statement executed successfully.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n\tfirst_name,\n\tlast_name,\n\temail,\n\tphone\n<span class=\"hljs-keyword\">FROM<\/span>\n\tleads;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"420\" height=\"40\" src=\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png\" alt=\"SQLite TRIGGER Leads Table\" class=\"wp-image-667\" srcset=\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png 420w, https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table-300x29.png 300w\" sizes=\"auto, (max-width: 420px) 100vw, 420px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">2) SQLite AFTER UPDATE trigger example<\/h3>\n\n\n\n<p>The phones and emails of the leads are so important&nbsp;that you can&#8217;t afford to lose this information. For example, someone accidentally updates the email or phone to the wrong ones or even delete it.<\/p>\n\n\n\n<p>To protect this valuable data, you use a trigger to log all changes which are made to the phone and email.<\/p>\n\n\n\n<p>First, <a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-create-table\/\">create a new table<\/a> called <code>lead_logs<\/code> to store the historical data.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> lead_logs (\n\t<span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">INTEGER<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n\told_id <span class=\"hljs-built_in\">int<\/span>,\n\tnew_id <span class=\"hljs-built_in\">int<\/span>,\n\told_phone <span class=\"hljs-built_in\">text<\/span>,\n\tnew_phone <span class=\"hljs-built_in\">text<\/span>,\n\told_email <span class=\"hljs-built_in\">text<\/span>,\n\tnew_email <span class=\"hljs-built_in\">text<\/span>,\n\tuser_action <span class=\"hljs-built_in\">text<\/span>,\n\tcreated_at <span class=\"hljs-built_in\">text<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, create an <code>AFTER UPDATE<\/code> trigger to log data to the <code>lead_logs<\/code> table whenever there is an update in the <code>email<\/code> or <code>phone<\/code> column.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> log_contact_after_update \n   <span class=\"hljs-keyword\">AFTER<\/span> <span class=\"hljs-keyword\">UPDATE<\/span> <span class=\"hljs-keyword\">ON<\/span> leads\n   <span class=\"hljs-keyword\">WHEN<\/span> old.phone &lt;&gt; new.phone\n        <span class=\"hljs-keyword\">OR<\/span> old.email &lt;&gt; new.email\n<span class=\"hljs-keyword\">BEGIN<\/span>\n\t<span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> lead_logs (\n\t\told_id,\n\t\tnew_id,\n\t\told_phone,\n\t\tnew_phone,\n\t\told_email,\n\t\tnew_email,\n\t\tuser_action,\n\t\tcreated_at\n\t)\n<span class=\"hljs-keyword\">VALUES<\/span>\n\t(\n\t\told.id,\n\t\tnew.id,\n\t\told.phone,\n\t\tnew.phone,\n\t\told.email,\n\t\tnew.email,\n\t\t<span class=\"hljs-string\">'UPDATE'<\/span>,\n\t\tDATETIME(<span class=\"hljs-string\">'NOW'<\/span>)\n\t) ;\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>You notice that in the condition in the <code>WHEN<\/code> clause specifies that the trigger is invoked only when there is a change in either email or phone column.<\/p>\n\n\n\n<p>Third, update the last name of <code>John<\/code> from <code>Doe<\/code> to <code>Smith<\/code>.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> leads\n<span class=\"hljs-keyword\">SET<\/span> \n   last_name = <span class=\"hljs-string\">'Smith'<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n   <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The trigger <code>log_contact_after_update<\/code>&nbsp;was not invoked because there was&nbsp;no change in email or phone.<\/p>\n\n\n\n<p>Fourth, update both email and phone of <code>John<\/code> to the new ones.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">UPDATE<\/span> leads\n<span class=\"hljs-keyword\">SET<\/span> \n   phone = <span class=\"hljs-string\">'4089998888'<\/span>,\n   email = <span class=\"hljs-string\">'john.smith@sqlitetutorial.net'<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n   <span class=\"hljs-keyword\">id<\/span> = <span class=\"hljs-number\">1<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you check the log table, you will see there is a new entry there.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n   old_phone, \n   new_phone, \n   old_email, \n   new_email, \n   user_action\n<span class=\"hljs-keyword\">FROM<\/span>\n   lead_logs;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"599\" height=\"41\" src=\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-After-Update-Trigger-Example.png\" alt=\"SQLite TRIGGER After Update Trigger Example\" class=\"wp-image-668\" srcset=\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-After-Update-Trigger-Example.png 599w, https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-After-Update-Trigger-Example-300x21.png 300w\" sizes=\"auto, (max-width: 599px) 100vw, 599px\" \/><\/figure>\n\n\n\n<p>You can develop the <code>AFTER INSERT<\/code> and <code>AFTER DELETE<\/code> triggers to log the data in the <code>lead_logs<\/code> table as an excercise.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">SQLite DROP TRIGGER statement<\/h2>\n\n\n\n<p>To drop an existing trigger, you use the <code>DROP TRIGGER<\/code> statement as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> &#91;<span class=\"hljs-keyword\">IF<\/span> <span class=\"hljs-keyword\">EXISTS<\/span>] trigger_name;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>First, specify the name of the trigger that you want to drop after the <code>DROP TRIGGER<\/code> keywords.<\/li><li>Second, use the <code>IF EXISTS<\/code> option to delete the trigger only if it exists.<\/li><\/ul>\n\n\n\n<p>Note that if you drop a table, SQLite will automatically drop all triggers associated with the table.<\/p>\n\n\n\n<p>For example, to remove the <code>validate_email_before_insert_leads<\/code> trigger, you use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> validate_email_before_insert_leads;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this tutorial, we have introduced you to SQLite triggers and show you how to create and drop triggers from the database.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"664\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\"\n\t\t\t\tdata-post-title=\"SQLite Trigger\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"664\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\"\n\t\t\t\tdata-post-title=\"SQLite Trigger\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial discusses the SQLite trigger, which is a\u00a0database object fired implicitly when data in a table is changed.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":4,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-664","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQLite Trigger: The Ultimate Guide You Don&#039;t Want To Miss<\/title>\n<meta name=\"description\" content=\"This tutorial discusses SQLite trigger, which is a\u00a0database object that is fired implicitly when data in a table is changed.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLite Trigger: The Ultimate Guide You Don&#039;t Want To Miss\" \/>\n<meta property=\"og:description\" content=\"This tutorial discusses SQLite trigger, which is a\u00a0database object that is fired implicitly when data in a table is changed.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\" \/>\n<meta property=\"og:site_name\" content=\"SQLite Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-11T02:41:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427\"},\"headline\":\"SQLite Trigger\",\"datePublished\":\"2015-12-21T08:17:24+00:00\",\"dateModified\":\"2020-04-11T02:41:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\"},\"wordCount\":870,\"image\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png\",\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\",\"url\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\",\"name\":\"SQLite Trigger: The Ultimate Guide You Don't Want To Miss\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png\",\"datePublished\":\"2015-12-21T08:17:24+00:00\",\"dateModified\":\"2020-04-11T02:41:55+00:00\",\"description\":\"This tutorial discusses SQLite trigger, which is a\u00a0database object that is fired implicitly when data in a table is changed.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage\",\"url\":\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png\",\"contentUrl\":\"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png\",\"width\":420,\"height\":40,\"caption\":\"SQLite TRIGGER Leads Table\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlitetutorial.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLite Trigger\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/#website\",\"url\":\"https:\/\/www.sqlitetutorial.net\/\",\"name\":\"SQLite Tutorial\",\"description\":\"A Step-by-step SQLite Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlitetutorial.net\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427\",\"name\":\"admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLite Trigger: The Ultimate Guide You Don't Want To Miss","description":"This tutorial discusses SQLite trigger, which is a\u00a0database object that is fired implicitly when data in a table is changed.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/","og_locale":"en_US","og_type":"article","og_title":"SQLite Trigger: The Ultimate Guide You Don't Want To Miss","og_description":"This tutorial discusses SQLite trigger, which is a\u00a0database object that is fired implicitly when data in a table is changed.","og_url":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/","og_site_name":"SQLite Tutorial","article_modified_time":"2020-04-11T02:41:55+00:00","og_image":[{"url":"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#article","isPartOf":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/"},"author":{"name":"admin","@id":"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427"},"headline":"SQLite Trigger","datePublished":"2015-12-21T08:17:24+00:00","dateModified":"2020-04-11T02:41:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/"},"wordCount":870,"image":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png","inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/","url":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/","name":"SQLite Trigger: The Ultimate Guide You Don't Want To Miss","isPartOf":{"@id":"https:\/\/www.sqlitetutorial.net\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png","datePublished":"2015-12-21T08:17:24+00:00","dateModified":"2020-04-11T02:41:55+00:00","description":"This tutorial discusses SQLite trigger, which is a\u00a0database object that is fired implicitly when data in a table is changed.","breadcrumb":{"@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#primaryimage","url":"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png","contentUrl":"https:\/\/www.sqlitetutorial.net\/wp-content\/uploads\/2015\/12\/SQLite-TRIGGER-Leads-Table.png","width":420,"height":40,"caption":"SQLite TRIGGER Leads Table"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlitetutorial.net\/sqlite-trigger\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlitetutorial.net\/"},{"@type":"ListItem","position":2,"name":"SQLite Trigger"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlitetutorial.net\/#website","url":"https:\/\/www.sqlitetutorial.net\/","name":"SQLite Tutorial","description":"A Step-by-step SQLite Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlitetutorial.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlitetutorial.net\/#\/schema\/person\/6d69b968cad0102e30d6694ed8dc6427","name":"admin"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages\/664","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/comments?post=664"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/pages\/664\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlitetutorial.net\/wp-json\/wp\/v2\/media?parent=664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}