Knowledge in Database Management System on viden.io

Knowledge in Database Management System

Database Management System for KIIT University

Study Material for Database Management System for KIIT University

Database Management System

Srijan Mehrotra

Database Management System

Information on basic topics of Database Management System including Programs on MySQL, RDBMS, Entity Relationship model,etc

Database Management System +2 more

Rituparna Mazumder

DBMS Lock based protocols

In this PDF we have complete information about lock based protocols

Database Management System

Saniya Begum

DBMS Timestamp Ordering Protocal

Information about timestamp ordering protocol

Database Management System

Mandumula Shivani

Triggers

Database Triggers Trigger defines an action the database should take when some database-related event occurs. Trig- gers may be used to supplement declarative referential integrity, to enforce complex business rules, or to audit changes to data. The code within a trigger, called a trigger body, is made up of PL/SQL blocks. It’s like a stored procedure that is fired when an insert, update or delete command is issued against associated table. The execution of triggers is transparent to the user. Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include insert, update, and delete. Updates of specific columns may also be used as triggering events.Because of their flexibility, triggers may supplement referential integrity; they should not be used toreplace it. When enforcing the business rules in an application, you should first rely on the declara- tive referential integrity available with Oracle; use triggers to enforce rules that cannot be coded through referential integrity.In other words, a trigger is a PL/SQL block that is associated with a table, stored in a database and executed in response to a specific data manipulation event. Triggers can be executed, or fired, in response to the following events: A row is inserted into a table A row in a table is updated A row in a table is deleted  R It is not possible to define a trigger to fire when a row is selected. A database trigger has three parts namely a trigger statement, a trigger body and a trigger restric- Tion Trigger statement specifies the DML statements like insert , update, delete and it fires the trigger body. It also specifies the table to which the trigger associated.Trigger body is a PL/SQL bock that is executed when a triggering statement is issued.Restrictions on a trigger can be achieved using the WHEN clause as shown in the syntax for creat-ing triggers. They can be included in the definition of a row trigger, wherein, the condition in the WHEN clause is evaluated for each row that is effected by the trigger.A trigger is a database object, like a table or an index. When you define a trigger, it becomes a part of the database and is always executed when the event for which it is defined occurs. It doesn’t matter if the event is triggered by someone typing in a SQL statement using SQL* Plus, running a Client/Server program that updates the database, or running a utility like Oracle’s SQL Loader in order to bulk-load data. Because of this, triggers serves as a choke point, allowing you to perform critical data validation or computations in response to database changes, no matter what the source. Types of Triggers A trigger ’s type is defined by the type of triggering transaction and by the level at which the trigger is executed. In the following sections, you will see descriptions of these classifications, along with relevant restrictions. Row-Level Triggers Row-level triggers execute once for each row in a transaction. Row-level triggers are the most common type of trigger; they are often used in data auditing applications. Row-level triggers are also useful for keeping distributed data in sync. Row-level triggers are created using the for each row clause in the create trigger command. Statement-Level Triggers Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into a table, then a statement-level trigger on that table would only be executed once. Statement-level triggers therefore are not often used for data-related activities; they are nor- mally used to enforce additional security measures on the types of transactions that may be per- formed on a table.Statement-level triggers are the default type of trigger created via the create trigger command. BEFORE and AFTER Triggers Because triggers are executed by events, they may be set to occur immediately before or after those events. Since the events that execute triggers include database transactions, trigger can be executed immediately before or after insert, update and delete. For database-level events, addi- tional restrictions apply; you cannot trigger an event to occur before a logon or startup takes place. Within the trigger, you can reference the old and new values invoked by the transaction. The access required for the old and new data may determine which type of trigger you need. 徹ld_ refers to the data, as it existed prior to the transaction; updates and deletes usually reference old values. New_ values are the data values that the transaction creates (such as the columns in an inserted record). If you need to set a column value in an inserted row via your trigger, then you need to use a BE- FORE INSERT trigger to access the 渡ew_ values. Using an AFTER INSERT trigger would not allow you to set the inserted value, since the row will already have been inserted into the table. AFTER row-level triggers are frequently used in auditing applications, since they do not fire until the row has been modified. The row’s successful modification implies that it has passed the referen- tial integrity constraints defined for that table.Together with commonly used four types, it gives a total of 12 possible trigger types, which are listed in the following Table. Note that the SELECT statement is the only data manipulation state-ment for which no triggers can be defined. Before update row  Before update statement  Before insert row  Before insert statement  Before delete row  Before delete statement  After update row  After update statement  After insert row  After insert statement  After delete row  After delete statement  Also note that one trigger can be defined to fire for more than one SQL statement. INSTEAD OF Triggers You can use INSTEAD OF triggers to tell Oracle what to do instead of performing the actions that invoked the trigger. For example, you could use an INSTEAD OF trigger on a view to redirect inserts into table or to update multiple tables that are part of a view. You can use INSTEAD OF triggers on either object views or relational views. For example, if a view involves a join of two tables, your ability to use the update command on records in the view is limited. However, if you use an INSTEAD OF trigger, you can tell Oracle how to update, delete, or insert records in the view’s underlying tables when a user attempts to change values via the view. The code in the INSTEAD OF trigger is executed in place of the update, delete,or insert command you enter. Uses of Triggers The possible uses for database triggers are varied and are limited only by your imagination. Some common uses are listed below:• Enforcing business rules Maintaining referential integrity  Enforcing security  Maintaining a historical log of changes  Generating column values, including primary key values  Replicating data  Syntax: Create [ or replace ] trigger [user.]trigger_name{ before | after | instead of } { delete | insert | update [ of column [, column] ・ } on [user.]{ Table | View } for each { row | statement } [ when (condition) ] PL/SQL Block Clearly, there is a great deal of flexibility in the design of a trigger. The before and after keywords indicate whether the trigger should be executed before or after the triggering transaction. If the instead of clause is used, the trigger’s code will be executed instead of the event that caused the trigger to be invoked. The delete, insert, and update keywords (the last of which may include a column list) indicate the type of data manipulation that will constitute a trigger event. When the for each row clause is used, the trigger will be a row-level trigger; otherwise, it will be a statement-level trigger. The when clause is used to further restrict when the trigger is executed. The restrictions enforced in the when clause may include checks of old and new data values. For example, suppose we want to monitor any adjustments to Salary column value that are greater than 10 percent. The following row-level BEFORE UPDATE trigger will be executed only if the new value of the salary column is more than 10 percent greater than its old value and add transac- tion details in audit table. This example also illustrates the use of the new keyword, which refers to the new value of the column, and the old keyword, which refers to the original value of the column. ExampleCreate or replace trigger emp_salary_update_row before update on emp for each row when (:New.Amount / :Old.Amount > 1.1) usr varchar2(20); Begin Select user into usr from dual; Insert into EMP_AUDIT values (:Old.salary, :New.salary, :Old.eno, usr, to_char(sysdate,’HH:MI’),sysdate); Commit; End; / Trigger created. Breaking the above created trigger command makes it easier to understand. Let’s do it: Create or replace trigger emp_salary_update_row the emp_salary_update_row is the trigger name, which indicates table name and it acts upon and the type of trigger. One can definetrigger with any valid name. before update on emp above statement indicates that this trigger applies to the Emp table and it will executed before update transactions have been committed to the database. for each row Because of above statement, the trigger will apply to each row in the transaction. If this clause is not used, then the trigger will execute at the statement level. The When clause adds further criteria to the triggering condition. The triggering event not only must be an update of the Ledger table, but also must reflect an increase of over 10 percent in the value of the Amount column when (New.Amount / Old.Amount > 1.1) The PL/SQL code shown in the following listing is the trigger body. The commands shown here are to be executed for every update of the emp table that passes the when condition. For this to suc- ceed, the EMP_AUDIT table must exist, and the owner must have been granted privileges on that table. This example inserts the old values from the emp record into EMP_AUDIT table before the employee record is updated in emp table. Structure of EMP_AUDIT table is as per follows. EMP_AUDIT eno number(5) old_salary number(9,2) new_salary number(9,2) user varchar2(20) tr_time varchar2(10) tr_date date Begin Select user into usr from dual; Insert into EMP_AUDIT values (:Old.eno, :Old.salary, :New.salary, usr, to_char(sysdate,’HH:MI’),sysdate); commit; End; / Above trigger makes the log of the emp table in the EMP_AUDIT table and maintains the track of updation in the table, the user name, Transaction Date, Transaction Time, All the old and new values of columns.R When referencing the New and Old keywords in the PL/SQL block, they are preceded by the colons(:) Using :Old and :New Values in Row Level Triggers When Row level trigger fires once per row processed by the triggering statement. Inside the trigger, you can access the row that is currently being processed. This is done through keywords :new and :old. Following describes what values are set to :Old and :New with the given triggering statement. Triggering Statement INSERT Old Undefined – all fields are NULL  New Values that will be inserted when the statement is complete  UPDATE Old Original values for the row before the update  New New values that will be updated when the statement is complete  DELETE Old Original values before the row is deleted  New Undefined – all fields are NULL  They are valid only within row level triggers and not in statement level triggers. :Old values are not available if the triggering statement is INSERT and :new values are not available if the triggering statement is DELETE. Each column is referenced by using the expression :Old.ColumnName or :New.ColumnName. if a column is not updated by the triggering update statement then :old and :new values remain the same. An Example of a Trigger with :New Suppose for a moment that you wanted to be sure that all department names were stored using uppercase letters. Perhaps you are doing this to facilitate searching on that field. Following example shows one way to do this with a trigger. Example Create or replace trigger upperdname before insert or update on dept for each rowBegin :new.dname := upper(:new.dname); End; / Trigger created. Example Following trigger does not allow the user to delete any row from a Try table. Create Or Replace Trigger delete_rest_trig Before Delete On Try Begin Raise_Application_Error(-20011,’UNABLE TO DELETE’); End;/ In the above example, a new Term is used i.e. Raise_Application_Error() Let’s discuss it in details: Customizing Error Conditions Oracle provides a procedure named raise_application_error that allows programmers to issue user- defined error messages. Syntax : Raise_Application_Error(Error_number, Message); Where Error_number is a negative integer in the range –20000 to –20999. and Message Is a string up to 2048 bytes in length An application can call 迭aise_Application_Error_ only from an executing stored subprogram like stored procedures and functions, database  triggers.Typically   迭aise_Application_Error_   is   used   in   database   triggers.迭aise_Application_Error_ ends the subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the application Within a single trigger, you may establish different error conditions. For each of the error conditions you define, you may select an error message that appears when the error occurs. The error num bers and messages that are displayed to the user are set via the Raise_Application_Error proce - dure, which may be called from within any trigger. Following example shows a statement-level BEFORE UPDATE or INSERT trigger on the Emptable. When a user attempts to Insert, modify or delete a row in the Emp table, this trigger is executed and checks two system conditions; that the day of the week is neither Saturday nor Sun-day, and that the Oracle username is other than user ‘ADMIN’ Example Create or replace trigger emp_rest before insert or update or delete on Emp Declare Weekend_Error Exception; Invalid_User Exception; Begin If to_char(SysDate, ‘DY’) in (‘sat’,’sun’) then Raise Weekend_Error; End if; If upper(User) != ‘ADMIN’ then Raise Invalid_User; End if; 

Database Management System

SingidiDammannapet Tejashwini