{"id":36588,"date":"2018-11-21T13:30:10","date_gmt":"2018-11-21T08:00:10","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=36588"},"modified":"2021-03-11T17:31:34","modified_gmt":"2021-03-11T12:01:34","slug":"triggers-in-sql","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/","title":{"rendered":"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages"},"content":{"rendered":"

In this tutorial, we will learn about the triggers in SQL. Let us now dive deep into the triggers world.<\/p>\n

What are SQL Triggers?<\/h3>\n

Triggers are programs that are available in the memory, with unique names made up of SQL queries which we need to fire on our database on and off.<\/p>\n

Triggers can be made to insert, update and delete statements in SQL. We have two types of triggers:<\/p>\n

1. Row Level Triggers<\/strong><\/p>\n

In a row-level trigger, the changes are put on all the rows on which the insert, delete, or update transaction is performed.<\/p>\n

If we have 1000 rows in a database and a delete operation is being run on them, then the trigger would also run 1000 times automatically. It is accessible in MySQL.<\/p>\n

2. Statement Level Triggers<\/strong><\/p>\n

In the statement-level triggers, the operation is under execution only once no matter how many rows are involved in the operation. These triggers are not accessible by MySQL.<\/p>\n

Why do we need Triggers in SQL?<\/h3>\n

We have seen what are triggers, now let us try to understand the need for triggers in SQL. Some of the aspects of using triggers in SQL database transactions are as follows:<\/p>\n

1. Allows the reuse of code.<\/p>\n

2. Helps us increase the computational costs by reducing result times.<\/p>\n

Syntax:<\/strong><\/p>\n

CREATE TRIGGER triggerName \r\nBEFORE\/AFTER \r\nINSERT\/UPDATE\/DELETE\r\nON tableName\r\nFOR EACH ROW SET operation;\r\n<\/pre>\n
We have six different variations of triggers.<\/p>\n
\"Six<\/a><\/p>\n
1. BEFORE INSERT:<\/strong> In these triggers, the operation on rows is under execution before any INSERT operation on the database.<\/p>\n
2. AFTER INSERT:<\/strong> In these triggers, the operation on rows is under execution after any INSERT operation on the database.<\/p>\n
3. BEFORE UPDATE:<\/strong> In these triggers, the operation on rows is under execution before an UPDATE operation on the database.<\/p>\n
4. AFTER UPDATE:<\/strong> In these triggers, the operation on rows is under execution after an UPDATE operation on the database.<\/p>\n
5. BEFORE DELETE:<\/strong> In these triggers, the operation on rows is under execution before any DELETE operation is run on the database.<\/p>\n
6. AFTER DELETE:<\/strong> In these triggers, the operation on rows is under execution after any DELETE operation on the database.<\/p>\n
Let us now look at how we use triggers with the help of an example.<\/p>\n
Example:<\/strong> Let us create a trigger on our table DataFlair whenever an update operation is run on our database.
\nLet us first initially view the contents of the DataFlair database.
\nQuery:<\/strong><\/p>\n
Use DataFlair;\r\nSELECT * FROM Dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
Syntax:<\/strong><\/p>\n
Use DataFlair;\r\n-- Trigger being created\r\nCREATE TRIGGER increase_experience AFTER UPDATE ON DataFlair\r\nFOR EACH ROW SET @experience = @experience + 1;\r\n-- Trigger put to use\r\nUPDATE DataFlair\r\nSET experience = 1\r\nWHERE experience = 0;\r\n-- View the result of Trigger\r\nSELECT * FROM DataFlair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Trigger<\/a><\/p>\n
Advantages of Triggers in SQL<\/h3>\n
Some of the prominent advantages of triggers are as follows:<\/p>\n
1. Helps us to automate the data alterations.<\/p>\n
2. Allows us to reuse the queries once written.<\/p>\n
3. Provides a method to check the data integrity of the database.<\/p>\n
4. Helps us to detect errors on the database level.<\/p>\n
5. Allows easy auditing of data.<\/p>\n
Disadvantages of Triggers<\/h3>\n
Some of the disadvantages of triggers in SQL are as follows:<\/p>\n
1. Increases the overhead costs of the server.<\/p>\n
2. Provides only extended validations i.e. not all validations are accessible in SQL triggers.<\/p>\n
3. Troubleshooting errors due to triggers is a tedious job.<\/p>\n
4. Can cause logical errors in the application even if a slight mistake in query exists.<\/p>\n
5. We could lose the original data if we set a wrong trigger by mistake.<\/p>\n
Summary<\/h3>\n
In this tutorial, we have seen in-depth all about triggers in SQL. We have seen we have two major triggers out of which one is functional in SQL.<\/p>\n
Further, we have seen the six major types of triggers which we can fire and use in SQL.<\/p>\n
Triggers are programs in the memory with unique names and help us to reuse the queries once written and kept safe in the memory.<\/p>\n
Moving on we have seen an example of how to create and use a trigger and then how we can view the result of a trigger. At last, we have seen the advantages and disadvantages of triggers in SQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will learn about the triggers in SQL. Let us now dive deep into the triggers world. What are SQL Triggers? Triggers are programs that are available in the memory, with...<\/p>\n","protected":false},"author":6,"featured_media":86942,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[16771,16769,16768,16773,16770,16766,16765,16767,16772],"class_list":["post-36588","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-database-triggers-sql-server","tag-sql-server-trigger-example","tag-sql-trigger-example","tag-sql-triggers","tag-trigger-in-dbms","tag-trigger-sql-server","tag-triggers-in-sql","tag-triggers-in-sql-server","tag-what-are-triggers-in-sql"],"yoast_head":"\nTriggers in SQL Tutorial | SQL Trigger Examples & Advantages - DataFlair<\/title>\n<meta name=\"description\" content=\"triggers in sql tutorial, What are SQL Triggers,sql trigger example,PL\/SQL trigger examples,advantages of Triggers in SQL,creating SQL Triggers\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages - DataFlair\" \/>\n<meta property=\"og:description\" content=\"triggers in sql tutorial, What are SQL Triggers,sql trigger example,PL\/SQL trigger examples,advantages of Triggers in SQL,creating SQL Triggers\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-21T08:00:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T12:01:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Triggers-in-SQL.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages - DataFlair","description":"triggers in sql tutorial, What are SQL Triggers,sql trigger example,PL\/SQL trigger examples,advantages of Triggers in SQL,creating SQL Triggers","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:\/\/data-flair.training\/blogs\/triggers-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages - DataFlair","og_description":"triggers in sql tutorial, What are SQL Triggers,sql trigger example,PL\/SQL trigger examples,advantages of Triggers in SQL,creating SQL Triggers","og_url":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-11-21T08:00:10+00:00","article_modified_time":"2021-03-11T12:01:34+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Triggers-in-SQL.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages","datePublished":"2018-11-21T08:00:10+00:00","dateModified":"2021-03-11T12:01:34+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/"},"wordCount":629,"commentCount":2,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Triggers-in-SQL.jpg","keywords":["database triggers sql server","sql server trigger example","sql trigger example","SQL Triggers","trigger in dbms","trigger sql server","triggers in sql","triggers in sql server","What are Triggers in SQL"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/","url":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/","name":"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Triggers-in-SQL.jpg","datePublished":"2018-11-21T08:00:10+00:00","dateModified":"2021-03-11T12:01:34+00:00","description":"triggers in sql tutorial, What are SQL Triggers,sql trigger example,PL\/SQL trigger examples,advantages of Triggers in SQL,creating SQL Triggers","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/triggers-in-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Triggers-in-SQL.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Triggers-in-SQL.jpg","width":1200,"height":628,"caption":"Triggers in SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/triggers-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SQL Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sql\/"},{"@type":"ListItem","position":3,"name":"Triggers in SQL Tutorial | SQL Trigger Examples & Advantages"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/36588","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=36588"}],"version-history":[{"count":4,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/36588\/revisions"}],"predecessor-version":[{"id":86947,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/36588\/revisions\/86947"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86942"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=36588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=36588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=36588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}