{"id":301,"date":"2012-06-04T16:40:34","date_gmt":"2012-06-04T13:40:34","guid":{"rendered":"http:\/\/fatihacar.com\/blog\/?p=301"},"modified":"2012-06-04T17:11:23","modified_gmt":"2012-06-04T14:11:23","slug":"create-trigger-in-oracle","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/create-trigger-in-oracle\/","title":{"rendered":"Create Trigger in Oracle"},"content":{"rendered":"<p>A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. For example, Oracle initiates an \u2018AFTER INSERT\u2019 trigger after an insert event has occurred or an \u2018AFTER UPDATE\u2019 trigger after an update event has occurred.<\/p>\n<p>You can write triggers that fire whenever one of the following operations occurs:<\/p>\n<ul>\n<li>DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user<\/li>\n<li>DDL statements (CREATE or ALTER primarily) issued either by a particular schema\/user or by any schema\/user in the database<\/li>\n<li>Database events, such as logon\/logoff, errors, or startup\/shutdown, also issued either by a particular schema\/user or by any schema\/user in the database<\/li>\n<\/ul>\n<p>Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:<\/p>\n<ul>\n<li>Automatically generate derived column values<\/li>\n<li>Prevent invalid transactions<\/li>\n<li>Enforce complex security authorizations<\/li>\n<li>Enforce referential integrity across nodes in a distributed database<\/li>\n<li>Enforce complex business rules<\/li>\n<li>Provide transparent event logging<\/li>\n<li>Provide auditing<\/li>\n<li>Maintain synchronous table replicates<\/li>\n<li>Gather statistics on table access<\/li>\n<li>Modify table data when DML statements are issued against views<\/li>\n<li>Publish information about database events, user events, and SQL statements to subscribing applications<\/li>\n<\/ul>\n<p><!--more--><br \/>\n<strong>After Insert Trigger<\/strong><\/p>\n<blockquote><p>CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON employees<br \/>\nFOR EACH ROW<br \/>\nDECLARE<br \/>\nBEGIN<br \/>\ninsert into emp_backup values (:new.empid, :new.fname, :new.lname);<br \/>\nEND;<\/p><\/blockquote>\n<p><strong>After Update Trigger<\/strong><\/p>\n<blockquote><p>SQL&gt; CREATE or REPLACE TRIGGER emp_after_update AFTER UPDATE OF name ON employees<br \/>\nFOR EACH ROW<br \/>\nDECLARE<br \/>\nBEGIN<br \/>\nupdate emp_backup<br \/>\nset name = :new.name<br \/>\nwhere empid = :old.empid;<br \/>\nEND;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. For example, Oracle initiates an \u2018AFTER INSERT\u2019 trigger after an insert event has occurred or an \u2018AFTER UPDATE\u2019 trigger after an update event has occurred. You can write triggers that fire whenever one of the&#8230;<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,6],"tags":[14,90],"class_list":["post-301","post","type-post","status-publish","format-standard","hentry","category-administration","category-oracle-sql","tag-database-administration","tag-oracle"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-4R","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=301"}],"version-history":[{"count":7,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/301\/revisions"}],"predecessor-version":[{"id":303,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/301\/revisions\/303"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=301"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}