{"id":942,"date":"2018-10-14T15:54:11","date_gmt":"2018-10-14T08:54:11","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=942"},"modified":"2020-04-11T20:13:05","modified_gmt":"2020-04-11T13:13:05","slug":"sql-server-create-trigger","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/","title":{"rendered":"SQL Server CREATE TRIGGER"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server <code>CREATE TRIGGER<\/code> statement to create a new trigger.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='introduction-to-sql-server-create-trigger-statement'>Introduction to SQL Server CREATE TRIGGER statement <a href=\"#introduction-to-sql-server-create-trigger-statement\" class=\"anchor\" id=\"introduction-to-sql-server-create-trigger-statement\" title=\"Anchor for Introduction to SQL Server &lt;code&gt;CREATE TRIGGER&lt;\/code&gt; statement\">#<\/a><\/h2>\n\n\n\n<p>The <code>CREATE TRIGGER<\/code> statement allows you to create a new trigger that is fired automatically whenever an event such as <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-insert\/\">INSERT<\/a><\/code>, <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-delete\/\"><code>DELETE<\/code><\/a>, or <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-update\/\">UPDATE<\/a><\/code> occurs against a table.<\/p>\n\n\n\n<p>The following illustrates the syntax of the <code>CREATE TRIGGER<\/code> statement:<\/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;schema_name.]trigger_name\n<span class=\"hljs-keyword\">ON<\/span> table_name\n<span class=\"hljs-keyword\">AFTER<\/span>  {&#91;<span class=\"hljs-keyword\">INSERT<\/span>],&#91;<span class=\"hljs-keyword\">UPDATE<\/span>],&#91;<span class=\"hljs-keyword\">DELETE<\/span>]}\n&#91;<span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">FOR<\/span> <span class=\"hljs-keyword\">REPLICATION<\/span>]\n<span class=\"hljs-keyword\">AS<\/span>\n{sql_statements}\n<\/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>The <code>schema_name<\/code> is the name of the schema to which the new trigger belongs. The schema name is optional.<\/li><li>The <code>trigger_name<\/code> is the user-defined name for the new trigger.<\/li><li>The <code>table_name<\/code> is the table to which the trigger applies.<\/li><li>The event is listed in the <code>AFTER<\/code> clause. The event could be <code>INSERT<\/code>, <code>UPDATE<\/code>, or <code>DELETE<\/code>. A single trigger can fire in response to one or more actions against the table.<\/li><li>The <code>NOT FOR REPLICATION<\/code> option instructs SQL Server not to fire the trigger when data modification is made as part of a replication process.<\/li><li>The <code>sql_statements<\/code> is one or more Transact-SQL used to carry out actions once an event occurs.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='virtual-tables-for-triggers-inserted-and-deleted'>&#8220;Virtual&#8221; tables for triggers: INSERTED and DELETED <a href=\"#virtual-tables-for-triggers-inserted-and-deleted\" class=\"anchor\" id=\"virtual-tables-for-triggers-inserted-and-deleted\" title=\"Anchor for &quot;Virtual&quot; tables for triggers: &lt;code&gt;INSERTED&lt;\/code&gt; and &lt;code&gt;DELETED&lt;\/code&gt;\">#<\/a><\/h2>\n\n\n\n<p>SQL Server provides two virtual tables that are available specifically for triggers called <code>INSERTED<\/code> and <code>DELETED<\/code> tables. SQL Server uses these tables to capture the data of the modified row before and after the event occurs.<\/p>\n\n\n\n<p>The following table shows the content of the <code>INSERTED<\/code> and <code>DELETED<\/code> tables before and after each event:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"\"><thead><tr><th>DML event<\/th><th>INSERTED table holds<\/th><th>DELETED table holds<\/th><\/tr><\/thead><tbody><tr><td>INSERT<\/td><td>rows to be inserted<\/td><td>empty<\/td><\/tr><tr><td>UPDATE<\/td><td>new rows modified by the update<\/td><td>existing rows modified by the update<\/td><\/tr><tr><td>DELETE<\/td><td>empty<\/td><td>rows to be deleted<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id='sql-server-create-trigger-example'>SQL Server CREATE TRIGGER example <a href=\"#sql-server-create-trigger-example\" class=\"anchor\" id=\"sql-server-create-trigger-example\" title=\"Anchor for SQL Server &lt;code&gt;CREATE TRIGGER&lt;\/code&gt; example\">#<\/a><\/h2>\n\n\n\n<p>Let&#8217;s look at an example of creating a new trigger. We will use the <code>production.products<\/code> table from the sample database for the demonstration.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"206\" height=\"169\" src=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/products.png\" alt=\"products\" class=\"wp-image-147\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id='1-create-a-table-for-logging-the-changes'>1) Create a table for logging the changes <a href=\"#1-create-a-table-for-logging-the-changes\" class=\"anchor\" id=\"1-create-a-table-for-logging-the-changes\" title=\"Anchor for 1) Create a table for logging the changes\">#<\/a><\/h3>\n\n\n\n<p>The following statement <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/\">creates a table<\/a> named <code>production.product_audits<\/code> to record information when an&nbsp;<code>INSERT<\/code> or <code>DELETE<\/code> event occurs against the <code>production.products<\/code> table:<\/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> production.product_audits(\n    change_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">IDENTITY<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    product_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    product_name <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    brand_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    category_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    model_year <span class=\"hljs-built_in\">SMALLINT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    list_price <span class=\"hljs-built_in\">DEC<\/span>(<span class=\"hljs-number\">10<\/span>,<span class=\"hljs-number\">2<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    updated_at DATETIME <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    operation <span class=\"hljs-built_in\">CHAR<\/span>(<span class=\"hljs-number\">3<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    <span class=\"hljs-keyword\">CHECK<\/span>(operation = <span class=\"hljs-string\">'INS'<\/span> <span class=\"hljs-keyword\">or<\/span> operation=<span class=\"hljs-string\">'DEL'<\/span>)\n);\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<p>The <code>production.product_audits<\/code> table has all the columns from the <code>production.products<\/code> table. In addition, it has a few more columns to record the changes e.g., <code>updated_at<\/code>, <code>operation<\/code>, and the <code>change_id<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='2-creating-an-after-dml-trigger'>2) Creating an after DML trigger <a href=\"#2-creating-an-after-dml-trigger\" class=\"anchor\" id=\"2-creating-an-after-dml-trigger\" title=\"Anchor for 2) Creating an after DML trigger\">#<\/a><\/h3>\n\n\n\n<p>First, to create a new trigger, you specify the name of the trigger and schema to which the trigger belongs in the <code>CREATE TRIGGER<\/code> clause:<\/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> production.trg_product_audit\n<\/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>Next, you specify the name of the table, which the trigger will fire when an event occurs, in the <code>ON<\/code> clause:<\/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\">ON production.products\n<\/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>Then, you list the one or more events which will call the trigger in the <code>AFTER<\/code> clause:<\/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\">AFTER <span class=\"hljs-keyword\">INSERT<\/span>, <span class=\"hljs-keyword\">DELETE<\/span>\n<\/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>The body of the trigger begins with the <code>AS<\/code> keyword:<\/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\">AS\n<span class=\"hljs-keyword\">BEGIN<\/span>\n<\/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<p>After that, inside the body of the trigger, you set the <code>SET NOCOUNT<\/code> to <code>ON<\/code> to suppress the number of rows affected messages from being returned whenever the trigger is fired.<\/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\">SET<\/span> NOCOUNT <span class=\"hljs-keyword\">ON<\/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>The trigger will <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-insert\/\">insert a row<\/a> into the <code>production.product_audits<\/code> table whenever a row is inserted into or deleted from the <code>production.products<\/code> table. The data for insert is fed from the <code>INSERTED<\/code> and <code>DELETED<\/code> tables via the <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-union\/\">UNION ALL<\/a><\/code> operator:<\/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\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span>\n    production.product_audits\n        (\n            product_id,\n            product_name,\n            brand_id,\n            category_id,\n            model_year,\n            list_price,\n            updated_at,\n            operation\n        )\n<span class=\"hljs-keyword\">SELECT<\/span>\n    i.product_id,\n    product_name,\n    brand_id,\n    category_id,\n    model_year,\n    i.list_price,\n    <span class=\"hljs-keyword\">GETDATE<\/span>(),\n    <span class=\"hljs-string\">'INS'<\/span>\n<span class=\"hljs-keyword\">FROM<\/span>\n    inserted <span class=\"hljs-keyword\">AS<\/span> i\n<span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\n    <span class=\"hljs-keyword\">SELECT<\/span>\n        d.product_id,\n        product_name,\n        brand_id,\n        category_id,\n        model_year,\n        d.list_price,\n        <span class=\"hljs-keyword\">getdate<\/span>(),\n        <span class=\"hljs-string\">'DEL'<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span>\n        deleted <span class=\"hljs-keyword\">AS<\/span> d;\n<\/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>The following put all parts together:<\/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\">CREATE<\/span> <span class=\"hljs-keyword\">TRIGGER<\/span> production.trg_product_audit\n<span class=\"hljs-keyword\">ON<\/span> production.products\n<span class=\"hljs-keyword\">AFTER<\/span> <span class=\"hljs-keyword\">INSERT<\/span>, <span class=\"hljs-keyword\">DELETE<\/span>\n<span class=\"hljs-keyword\">AS<\/span>\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">SET<\/span> NOCOUNT <span class=\"hljs-keyword\">ON<\/span>;\n    <span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> production.product_audits(\n        product_id, \n        product_name,\n        brand_id,\n        category_id,\n        model_year,\n        list_price, \n        updated_at, \n        operation\n    )\n    <span class=\"hljs-keyword\">SELECT<\/span>\n        i.product_id,\n        product_name,\n        brand_id,\n        category_id,\n        model_year,\n        i.list_price,\n        <span class=\"hljs-keyword\">GETDATE<\/span>(),\n        <span class=\"hljs-string\">'INS'<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span>\n        inserted i\n    <span class=\"hljs-keyword\">UNION<\/span> <span class=\"hljs-keyword\">ALL<\/span>\n    <span class=\"hljs-keyword\">SELECT<\/span>\n        d.product_id,\n        product_name,\n        brand_id,\n        category_id,\n        model_year,\n        d.list_price,\n        <span class=\"hljs-keyword\">GETDATE<\/span>(),\n        <span class=\"hljs-string\">'DEL'<\/span>\n    <span class=\"hljs-keyword\">FROM<\/span>\n        deleted d;\n<span class=\"hljs-keyword\">END<\/span>\n<\/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>Finally, you execute the whole statement to create the trigger. Once the trigger is created, you can find it under the triggers folder of the table as shown in the following picture:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"376\" height=\"357\" src=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-Example.jpg\" alt=\"SQL Server Create Trigger Example\" class=\"wp-image-943\" srcset=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-Example.jpg 376w, https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-Example-300x285.jpg 300w\" sizes=\"auto, (max-width: 376px) 100vw, 376px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id='3-testing-the-trigger'>3) Testing the trigger <a href=\"#3-testing-the-trigger\" class=\"anchor\" id=\"3-testing-the-trigger\" title=\"Anchor for 3) Testing the trigger\">#<\/a><\/h3>\n\n\n\n<p>The following statement <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-insert\/\">inserts a new row<\/a> into the <code>production.products<\/code> table:<\/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\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> production.products(\n    product_name, \n    brand_id, \n    category_id, \n    model_year, \n    list_price\n)\n<span class=\"hljs-keyword\">VALUES<\/span> (\n    <span class=\"hljs-string\">'Test product'<\/span>,\n    <span class=\"hljs-number\">1<\/span>,\n    <span class=\"hljs-number\">1<\/span>,\n    <span class=\"hljs-number\">2018<\/span>,\n    <span class=\"hljs-number\">599<\/span>\n);\n<\/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>Because of the <code>INSERT<\/code> event, the <code>production.trg_product_audit<\/code> trigger of <code>production.products<\/code> table was fired.<\/p>\n\n\n\n<p>Let&#8217;s examine the contents of the <code>production.product_audits<\/code> table:<\/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    * \n<span class=\"hljs-keyword\">FROM<\/span> \n    production.product_audits;\n<\/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<p>Here is the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"684\" height=\"44\" src=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-After-Insert-Example.jpg\" alt=\"SQL Server Create Trigger - After Insert Example\" class=\"wp-image-944\" srcset=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-After-Insert-Example.jpg 684w, https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-After-Insert-Example-300x19.jpg 300w\" sizes=\"auto, (max-width: 684px) 100vw, 684px\" \/><\/figure>\n\n\n\n<p>The following statement deletes a row from the <code>production.products<\/code> table:<\/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\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> \n    production.products\n<span class=\"hljs-keyword\">WHERE<\/span> \n    product_id = <span class=\"hljs-number\">322<\/span>;\n<\/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>As expected, the trigger was fired and inserted the deleted row into the <code>production.product_audits<\/code> table:<\/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\">SELECT<\/span> \n    * \n<span class=\"hljs-keyword\">FROM<\/span> \n    production.product_audits;\n<\/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>The following picture shows the output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"673\" height=\"60\" src=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-After-delete-Example.jpg\" alt=\"SQL Server Create Trigger - After delete Example\" class=\"wp-image-945\" srcset=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-After-delete-Example.jpg 673w, https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-Create-Trigger-After-delete-Example-300x27.jpg 300w\" sizes=\"auto, (max-width: 673px) 100vw, 673px\" \/><\/figure>\n\n\n\n<p>In this tutorial, you have learned how to create a trigger in SQL Server to respond to one or more events such as insert and delete.<\/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=\"942\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/\"\n\t\t\t\tdata-post-title=\"SQL Server CREATE 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=\"942\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/\"\n\t\t\t\tdata-post-title=\"SQL Server CREATE 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>In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":883,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-942","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Server CREATE TRIGGER<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.\" \/>\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.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server CREATE TRIGGER\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-11T13:13:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/products.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=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/\",\"name\":\"SQL Server CREATE TRIGGER\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/wp-content\\\/uploads\\\/products.png\",\"datePublished\":\"2018-10-14T08:54:11+00:00\",\"dateModified\":\"2020-04-11T13:13:05+00:00\",\"description\":\"In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/wp-content\\\/uploads\\\/products.png\",\"contentUrl\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/wp-content\\\/uploads\\\/products.png\",\"width\":206,\"height\":169,\"caption\":\"products\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/sql-server-create-trigger\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Triggers\",\"item\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-triggers\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Server CREATE TRIGGER\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\",\"name\":\"SQL Server Tutorial\",\"description\":\"The Practical SQL Server Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server CREATE TRIGGER","description":"In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.","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.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server CREATE TRIGGER","og_description":"In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.","og_url":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/","og_site_name":"SQL Server Tutorial","article_modified_time":"2020-04-11T13:13:05+00:00","og_image":[{"url":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/products.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/","url":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/","name":"SQL Server CREATE TRIGGER","isPartOf":{"@id":"https:\/\/www.sqlservertutorial.net\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/products.png","datePublished":"2018-10-14T08:54:11+00:00","dateModified":"2020-04-11T13:13:05+00:00","description":"In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.","breadcrumb":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/#primaryimage","url":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/products.png","contentUrl":"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/products.png","width":206,"height":169,"caption":"products"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-create-trigger\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlservertutorial.net\/"},{"@type":"ListItem","position":2,"name":"SQL Server Triggers","item":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/"},{"@type":"ListItem","position":3,"name":"SQL Server CREATE TRIGGER"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlservertutorial.net\/#website","url":"https:\/\/www.sqlservertutorial.net\/","name":"SQL Server Tutorial","description":"The Practical SQL Server Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlservertutorial.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/942","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/comments?post=942"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/942\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/883"}],"wp:attachment":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/media?parent=942"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}