{"id":225,"date":"2018-07-19T09:12:28","date_gmt":"2018-07-19T09:12:28","guid":{"rendered":"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/?post_type=chapter&#038;p=225"},"modified":"2018-07-19T11:29:28","modified_gmt":"2018-07-19T11:29:28","slug":"active-databases","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/chapter\/active-databases\/","title":{"rendered":"Active Databases"},"content":{"raw":"Conventional (Passive Databases)\r\n<ul>\r\n \t<li>Data model, usually relational<\/li>\r\n \t<li>Transaction model - Passive update principle Example of further real world problem<\/li>\r\n \t<li>Inventory control - reordering of items when quantity in stock falls below threshold.<\/li>\r\n \t<li>Travel waiting list - book ticket as soon as right kind is available<\/li>\r\n \t<li>Stock market - Buy\/sell stocks when price below\/above threshold<\/li>\r\n<\/ul>\r\nRules\r\n<ul>\r\n \t<li style=\"text-align: justify\">specify actions that are automatically triggered by certain events<\/li>\r\n \t<li style=\"text-align: justify\">generalised model: EVENT-CONDITION-ACTION (ECA)<\/li>\r\n \t<li style=\"text-align: justify\">event(s): database update operation(s)<\/li>\r\n \t<li style=\"text-align: justify\">condition: determines whether the rule is to be executed after the event occurred<\/li>\r\n \t<li style=\"text-align: justify\">action: action to be taken<\/li>\r\n \t<li style=\"text-align: justify\">SQL command(s)<\/li>\r\n \t<li style=\"text-align: justify\">external program<\/li>\r\n \t<li style=\"text-align: justify\">rules are sometimes called triggers<\/li>\r\n \t<li style=\"text-align: justify\">however, some systems make distinctions between them (Postgres)<\/li>\r\n<\/ul>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0Triggers<\/p>\r\n&nbsp;\r\n\r\nWhat is a trigger?\r\n<ul>\r\n \t<li style=\"text-align: justify\">Trigger is like a procedure that is automatically invoked by the DBMS in response to specified changes to data base.<\/li>\r\n \t<li style=\"text-align: justify\">Trigger is like a \u2018Daemon that monitors a data base, and is executed when the data base is modified in a way that matches the event specification.<\/li>\r\n \t<li style=\"text-align: justify\">A data base that has a set of associated triggers is called an active data base.<\/li>\r\n<\/ul>\r\nEvent-Condition-Action (ECA)\r\n<ul>\r\n \t<li>Event occurs in databases<\/li>\r\n \t<li>addition of new row, deletion of row by DBMS<\/li>\r\n \t<li>Conditions are checked<\/li>\r\n \t<li>SQL condition<\/li>\r\n \t<li>Actions are executed if conditions are satisfied<\/li>\r\n \t<li>SQL + procedures<\/li>\r\n \t<li>All data actions performed by the trigger execute within the same transaction in which the trigger fires,<\/li>\r\n \t<li>Cannot contain transaction control statements (COMMIT,SAVEPOINT, ROLLBACK)<\/li>\r\n<\/ul>\r\nMotivation\r\n\r\n&nbsp;\r\n<p style=\"text-align: center\"><img class=\"size-full wp-image-226 aligncenter\" src=\"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106.png\" alt=\"\" width=\"624\" height=\"198\" \/><\/p>\r\nDatabase Triggers in SQL\r\n<ul>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Not specified in SQL-92, but standardized in SQL3<\/span>(SQL1999)<\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Available in most enterprise DBMSs (Oracle, IBM DB2, MS SQL server) and some public domain DBMSs (Postgres)<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">but not present in smaller desktop (Oracle Lite) and public domain DBMS (MySQL)<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">S<\/span><span style=\"text-align: initial;font-size: 1em\">ome vendor DBMS permit native extensions to SQL for specifying the triggers<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"font-size: 1em\">E.g. PL\/SQL in Oracle, Transact SQL in MS SQL Server<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"font-size: 1em\">Some DBMS also general purpose programming language instead of SQL<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"font-size: 1em\">Eg. C\/C++ in Poet, Java in Oracle, C#\/VB in SQL Server<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"font-size: 1em\">Some DBMS extend the triggers beyond tables<\/span><\/li>\r\n \t<li style=\"text-align: justify\"><span style=\"font-size: 1em\">for example also to views as in Oracle<\/span><\/li>\r\n<\/ul>\r\nTypes of SQL Triggers\r\n<ul>\r\n \t<li>How many times should the trigger body execute when the triggering event takes place?<\/li>\r\n \t<li>Per statement: the trigger body executes once for the triggering event. This is the default.<\/li>\r\n \t<li>For each row: the trigger body executes once for each row affected by the triggering event.<\/li>\r\n<\/ul>\r\nIssues about active databases\r\n<ul>\r\n \t<li>general model (remember): ECA<\/li>\r\n \t<li>event<\/li>\r\n \t<li>in general INSERT, UPDATE, DELETE<\/li>\r\n \t<li>can be more specific (see \u201cupdate of salary on employee\u201d)<\/li>\r\n \t<li>condition<\/li>\r\n \t<li>could be empty when solely the event triggers<\/li>\r\n \t<li>row-level rule: the rule is triggered for each tuple for which the condition is satisfied<\/li>\r\n \t<li>statement-level rule: the rule is triggered only once for each triggering statement<\/li>\r\n \t<li>action<\/li>\r\n \t<li>SQL statement or external procedure<\/li>\r\n<\/ul>\r\nStatement and Row Triggers\r\n\r\n&nbsp;\r\n\r\nExample 1: Monitoring Statement Events\r\n\r\nSQL&gt; INSERT INTO dept (deptno, dname, loc) 2\u00a0 VALUES (50, 'EDUCATION', 'NEW YORK');\r\n\r\nExecute only once even if multiple rows affected\r\n\r\n&nbsp;\r\n\r\nExample 2: Monitoring Row Events\r\n\r\nSQL&gt; UPDATE emp\r\n\r\n2\u00a0\u00a0\u00a0\u00a0 SET sal = sal * 1.1\r\n\r\n3\u00a0\u00a0\u00a0\u00a0 WHERE deptno = 30;\r\n\r\n&nbsp;\r\n\r\nExecute for each row of table affected by event\r\n\r\n&nbsp;\r\n\r\nFiring Sequence of Database\r\n\r\n&nbsp;\r\n<p style=\"text-align: center\"><img class=\"size-full wp-image-227 aligncenter\" src=\"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107.png\" alt=\"\" width=\"664\" height=\"350\" \/><\/p>\r\n<strong>SUMMARY<\/strong>\r\n<ul>\r\n \t<li>Conventional (Passive) vs Active Databases (Triggers)<\/li>\r\n \t<li>Active DBMSs embed situation-action rules in database.<\/li>\r\n \t<li>Event \u2013 Condition \u2013 Action Rules.<\/li>\r\n \t<li>Types of Triggers<\/li>\r\n \t<li>\u00a0Row-level trigger<\/li>\r\n \t<li>\u00a0Statement Level Trigger<\/li>\r\n<\/ul>\r\n<p style=\"text-align: center\"><img class=\"size-full wp-image-228 aligncenter\" src=\"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108.png\" alt=\"\" width=\"583\" height=\"312\" \/><\/p>","rendered":"<p>Conventional (Passive Databases)<\/p>\n<ul>\n<li>Data model, usually relational<\/li>\n<li>Transaction model &#8211; Passive update principle Example of further real world problem<\/li>\n<li>Inventory control &#8211; reordering of items when quantity in stock falls below threshold.<\/li>\n<li>Travel waiting list &#8211; book ticket as soon as right kind is available<\/li>\n<li>Stock market &#8211; Buy\/sell stocks when price below\/above threshold<\/li>\n<\/ul>\n<p>Rules<\/p>\n<ul>\n<li style=\"text-align: justify\">specify actions that are automatically triggered by certain events<\/li>\n<li style=\"text-align: justify\">generalised model: EVENT-CONDITION-ACTION (ECA)<\/li>\n<li style=\"text-align: justify\">event(s): database update operation(s)<\/li>\n<li style=\"text-align: justify\">condition: determines whether the rule is to be executed after the event occurred<\/li>\n<li style=\"text-align: justify\">action: action to be taken<\/li>\n<li style=\"text-align: justify\">SQL command(s)<\/li>\n<li style=\"text-align: justify\">external program<\/li>\n<li style=\"text-align: justify\">rules are sometimes called triggers<\/li>\n<li style=\"text-align: justify\">however, some systems make distinctions between them (Postgres)<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u00a0 \u00a0Triggers<\/p>\n<p>&nbsp;<\/p>\n<p>What is a trigger?<\/p>\n<ul>\n<li style=\"text-align: justify\">Trigger is like a procedure that is automatically invoked by the DBMS in response to specified changes to data base.<\/li>\n<li style=\"text-align: justify\">Trigger is like a \u2018Daemon that monitors a data base, and is executed when the data base is modified in a way that matches the event specification.<\/li>\n<li style=\"text-align: justify\">A data base that has a set of associated triggers is called an active data base.<\/li>\n<\/ul>\n<p>Event-Condition-Action (ECA)<\/p>\n<ul>\n<li>Event occurs in databases<\/li>\n<li>addition of new row, deletion of row by DBMS<\/li>\n<li>Conditions are checked<\/li>\n<li>SQL condition<\/li>\n<li>Actions are executed if conditions are satisfied<\/li>\n<li>SQL + procedures<\/li>\n<li>All data actions performed by the trigger execute within the same transaction in which the trigger fires,<\/li>\n<li>Cannot contain transaction control statements (COMMIT,SAVEPOINT, ROLLBACK)<\/li>\n<\/ul>\n<p>Motivation<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-226 aligncenter\" src=\"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106.png\" alt=\"\" width=\"624\" height=\"198\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106.png 624w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106-300x95.png 300w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106-65x21.png 65w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106-225x71.png 225w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-106-350x111.png 350w\" sizes=\"auto, (max-width: 624px) 100vw, 624px\" \/><\/p>\n<p>Database Triggers in SQL<\/p>\n<ul>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Not specified in SQL-92, but standardized in SQL3<\/span>(SQL1999)<\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">Available in most enterprise DBMSs (Oracle, IBM DB2, MS SQL server) and some public domain DBMSs (Postgres)<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">but not present in smaller desktop (Oracle Lite) and public domain DBMS (MySQL)<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"text-align: initial;font-size: 1em\">S<\/span><span style=\"text-align: initial;font-size: 1em\">ome vendor DBMS permit native extensions to SQL for specifying the triggers<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"font-size: 1em\">E.g. PL\/SQL in Oracle, Transact SQL in MS SQL Server<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"font-size: 1em\">Some DBMS also general purpose programming language instead of SQL<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"font-size: 1em\">Eg. C\/C++ in Poet, Java in Oracle, C#\/VB in SQL Server<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"font-size: 1em\">Some DBMS extend the triggers beyond tables<\/span><\/li>\n<li style=\"text-align: justify\"><span style=\"font-size: 1em\">for example also to views as in Oracle<\/span><\/li>\n<\/ul>\n<p>Types of SQL Triggers<\/p>\n<ul>\n<li>How many times should the trigger body execute when the triggering event takes place?<\/li>\n<li>Per statement: the trigger body executes once for the triggering event. This is the default.<\/li>\n<li>For each row: the trigger body executes once for each row affected by the triggering event.<\/li>\n<\/ul>\n<p>Issues about active databases<\/p>\n<ul>\n<li>general model (remember): ECA<\/li>\n<li>event<\/li>\n<li>in general INSERT, UPDATE, DELETE<\/li>\n<li>can be more specific (see \u201cupdate of salary on employee\u201d)<\/li>\n<li>condition<\/li>\n<li>could be empty when solely the event triggers<\/li>\n<li>row-level rule: the rule is triggered for each tuple for which the condition is satisfied<\/li>\n<li>statement-level rule: the rule is triggered only once for each triggering statement<\/li>\n<li>action<\/li>\n<li>SQL statement or external procedure<\/li>\n<\/ul>\n<p>Statement and Row Triggers<\/p>\n<p>&nbsp;<\/p>\n<p>Example 1: Monitoring Statement Events<\/p>\n<p>SQL&gt; INSERT INTO dept (deptno, dname, loc) 2\u00a0 VALUES (50, &#8216;EDUCATION&#8217;, &#8216;NEW YORK&#8217;);<\/p>\n<p>Execute only once even if multiple rows affected<\/p>\n<p>&nbsp;<\/p>\n<p>Example 2: Monitoring Row Events<\/p>\n<p>SQL&gt; UPDATE emp<\/p>\n<p>2\u00a0\u00a0\u00a0\u00a0 SET sal = sal * 1.1<\/p>\n<p>3\u00a0\u00a0\u00a0\u00a0 WHERE deptno = 30;<\/p>\n<p>&nbsp;<\/p>\n<p>Execute for each row of table affected by event<\/p>\n<p>&nbsp;<\/p>\n<p>Firing Sequence of Database<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-227 aligncenter\" src=\"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107.png\" alt=\"\" width=\"664\" height=\"350\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107.png 664w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107-300x158.png 300w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107-65x34.png 65w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107-225x119.png 225w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-107-350x184.png 350w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/p>\n<p><strong>SUMMARY<\/strong><\/p>\n<ul>\n<li>Conventional (Passive) vs Active Databases (Triggers)<\/li>\n<li>Active DBMSs embed situation-action rules in database.<\/li>\n<li>Event \u2013 Condition \u2013 Action Rules.<\/li>\n<li>Types of Triggers<\/li>\n<li>\u00a0Row-level trigger<\/li>\n<li>\u00a0Statement Level Trigger<\/li>\n<\/ul>\n<p style=\"text-align: center\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-228 aligncenter\" src=\"http:\/\/csp4.epgpbooks.inflibnet.ac.in\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108.png\" alt=\"\" width=\"583\" height=\"312\" srcset=\"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108.png 583w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108-300x161.png 300w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108-65x35.png 65w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108-225x120.png 225w, https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-content\/uploads\/sites\/47\/2018\/07\/a2-108-350x187.png 350w\" sizes=\"auto, (max-width: 583px) 100vw, 583px\" \/><\/p>\n","protected":false},"author":4,"menu_order":28,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-r-baskaran"],"pb_section_license":""},"chapter-type":[],"contributor":[58],"license":[],"class_list":["post-225","chapter","type-chapter","status-publish","hentry","contributor-dr-r-baskaran"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/225","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/users\/4"}],"version-history":[{"count":1,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/225\/revisions"}],"predecessor-version":[{"id":229,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/225\/revisions\/229"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapters\/225\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/media?parent=225"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/pressbooks\/v2\/chapter-type?post=225"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/contributor?post=225"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/csp4\/wp-json\/wp\/v2\/license?post=225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}