{"id":4011,"date":"2025-06-07T09:41:37","date_gmt":"2025-06-07T09:41:37","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4011"},"modified":"2025-06-07T09:41:38","modified_gmt":"2025-06-07T09:41:38","slug":"sql-triggers","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-triggers\/","title":{"rendered":"SQL Triggers"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">A <strong>Trigger<\/strong> is a special kind of stored procedure that automatically executes in response to certain events on a table or view, such as inserts, updates, or deletes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>What is a Trigger?<\/strong><br>Triggers are used to enforce business rules, maintain audit logs, or validate data automatically when data modification events occur.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Basic Types of Triggers<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>BEFORE<\/strong>: Executes before the data change (useful to validate or modify data)<\/li>\n\n\n\n<li><strong>AFTER<\/strong>: Executes after the data change (useful for logging or cascading changes)<\/li>\n\n\n\n<li><strong>INSTEAD OF<\/strong>: (mostly for views) Replaces the triggering operation<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Basic Syntax Example (MySQL)<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TRIGGER trigger_name\nBEFORE INSERT ON table_name\nFOR EACH ROW\nBEGIN\n   -- Trigger logic here\n   SET NEW.column_name = UPPER(NEW.column_name);\nEND;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Example: Audit Log on UPDATE<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TRIGGER update_audit\nAFTER UPDATE ON employees\nFOR EACH ROW\nBEGIN\n   INSERT INTO audit_log(employee_id, changed_on)\n   VALUES (NEW.employee_id, NOW());\nEND;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Use Cases<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Automatic data validation<\/li>\n\n\n\n<li>Maintaining audit trails<\/li>\n\n\n\n<li>Enforcing referential integrity rules<\/li>\n\n\n\n<li>Synchronous replication of data<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udd39 <strong>Important Notes<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Triggers run automatically and cannot be called directly.<\/li>\n\n\n\n<li>Avoid complex logic inside triggers to prevent performance issues.<\/li>\n\n\n\n<li>Triggers differ in syntax and capabilities across DBMS (MySQL, Oracle, SQL Server, PostgreSQL).<\/li>\n\n\n\n<li>Test triggers carefully to avoid infinite loops or unintended side effects.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83e\udde0 <strong>Quick Recap<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Point<\/th><th>Explanation<\/th><\/tr><\/thead><tbody><tr><td>Definition<\/td><td>Auto-executed procedure on data events<\/td><\/tr><tr><td>Event Types<\/td><td>BEFORE, AFTER, INSTEAD OF<\/td><\/tr><tr><td>Common Uses<\/td><td>Validation, auditing, enforcing rules<\/td><\/tr><tr><td>Execution<\/td><td>Automatic, tied to INSERT\/UPDATE\/DELETE<\/td><\/tr><tr><td>Caution<\/td><td>Can affect performance if overused<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Triggers help automate database logic but use them wisely to maintain good performance and clarity.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A Trigger is a special kind of stored procedure that automatically executes in response to certain events on a table or view, such as inserts, updates, or deletes. \ud83d\udd39 What is a Trigger?Triggers are used to enforce business rules, maintain audit logs, or validate data automatically when data modification events occur. \ud83d\udd39 Basic Types of [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-4011","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4011","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}