Please Click Here to continue from the background.
Integrity Constraints are the most convenient form of implementation of data validation rules, as they are purely declarative and involve no programming. They check the existing data in the columns for validity, in case if they are created after the tables are populated with the data or if for some reasons they are required to be disabled and re-enabled; they leave no chance that the column may contain any invalid data. Hence, the constraints are considered to be the most reliable way of implementing data integrity.
However, they are not without limitations. For example, constraints can not implement conditional validation or they do not have capability to perform pragmatically minor transformations to the data to make it compatible with the rule of the constraint. They may only fail the statement / transaction if the data does not conform to the rule of the constraint. How if you want that the names of the employees, departments and locations always have to be in upper case? How if you want the salary of the employees not to cross 5000 for those working in department number 30 only? How if any department in your company should have no more employees than 100? .... The list of such requirements may be long, for which the integrity constraints offer no solution. Those limitations on data (columns) however may be implemented using the triggers -
What are Triggers? -
Triggers are the programs, written in a back end programming language and reside in the database, which execute automatically in response to an event in the database.
Triggers come in different types, primarily according to their events. The triggers which fire (read execute) for system related events are called system triggers, while those which fire for a DML events on tables, are called as DML triggers. Some databases allow to write triggers to fire on DML events on views, are called as instead-of triggers. While it may not be possible to provide an entire lesson on triggers here in this small post, we shall discuss in brief about the DML triggers, the type mostly used and having context with our present topic. Our discussion is in reference to Oracle as model database, however minor differences may emerge in the other databases. The readers are requested to post freely their comments about those differences here to enrich the reading experience of the visitors.
Triggers come in different types, primarily according to their events. The triggers which fire (read execute) for system related events are called system triggers, while those which fire for a DML events on tables, are called as DML triggers. Some databases allow to write triggers to fire on DML events on views, are called as instead-of triggers. While it may not be possible to provide an entire lesson on triggers here in this small post, we shall discuss in brief about the DML triggers, the type mostly used and having context with our present topic. Our discussion is in reference to Oracle as model database, however minor differences may emerge in the other databases. The readers are requested to post freely their comments about those differences here to enrich the reading experience of the visitors.
DML Triggers - A Primer -
The trigger (read DML triggers, in all forthcoming references), may be created to fire at "row level" or "statement level"/"table level". The former, identified by the presence of "for each row" clause, executes for every row affected by the DML statement, for which it fires, the latter fires for only once irrespective of the number of rows affected by its conjugating DML statement. Row Level triggers are useful in the situation when triggering action has to refer to or use the row level values, since such reference of row level values is only possible in row level trigger (being firing for each row). Oracle provides :NEW and :OLD to refer to the new and old state of record being affected by the DML in the row level trigger. Statement level triggers can't refer to :NEW and :OLD. Any of these may be timed to fire "before" or "after". The "before" timed trigger performs its action before the conjugating DML statement performs it operation, so that automatically defines what "after" timed trigger means. The "before" trigger may be used in situations where some data transformations on the data in transition may be required or the operation is required to be aborted by aborting the trigger, whereas "after" trigger may be used in situations where the trigger action may depend upon the outcome of the DML statement or has to refer to such outcome. Oracle allows triggers to name columns for the update event, so that the trigger is saved from firing unnecessarily while only other columns are being updated. The triggers may have optionally a condition specified under a "When" clause only for which the trigger should fire. The clause can be used only in row level triggers, as this restricting clause can refer to new and old versions of the row(s). Oracle allows the trigger to be combined for insert, update and delete events or any combination thereof, if the triggers had to be same level (row or statement) and timed to fire similarly (before or after) if they were to be written separately for each event. The particular code for each separate event can be made to execute by using boolean built-in variables, which Oracle calls as "predicates" viz - inserting, updating and deleting.
That was rather a highly stuffed primer on the triggers and by no means can substitute a complete explanation, but the same has only been provided for the base, so that users will not feel the forthcoming discussion as alien. A dedicated post to triggers may sometime come in near future but meanwhile you may refer to this link.
Trigger Examples - Implementation of Data Integrity -
Example 1 - In the first example we shall see the implementation of a constraint, where we want that the data in the "dname" and "location" columns of "department" table should be always in upper case. Whereas we could simply put a check constraint, the problem with constraint would be that, it will fail the update or insert statement if the data is not provided in upper case by the user, thus consume time and resources in rejection and users then resorting to re-execute the operation. Whereas now, in the trigger, we implicitly convert the case of the data provided by the user and so trigger will ensure that the data which makes it to the columns is always uppercase, no matter in what case it was provided by the user. So the example -
create or replace trigger upper_case_dept_trig
Explanation : The code in red is triggering statement - specifies the timing for the trigger "before" in this case and events which are "insert" and "update" (with column list, in absence of such column list it will fire for all updates on any column(s)) and table name "department". In blue is the clause which tells that this is "row level" trigger, since we require to manipulate the data provided by the user, we are using "before" timed and its reference may be only available from :NEW, we are using a row level trigger. The code in grey is the trigger action - in this case converting the user provided data into upper case (upper is a function) and storing it in same vars so ensuring that it will always be upper case.
Example 2 - In the second example we ensure that the salary of any employee may not be greater than limit of 5000 only in department number 30. Remember that, if any employee is having salary greater than 5000 in department number 30, before the trigger is written or some user makes it so when the trigger is in the disabled state, then the trigger CAN NOT detect this, unlike the integrity constraint. However we may not easily apply this rule using integrity constraint. So the example -
create or replace trigger limitsal
before insert or update of deptno, salary on employee
for each row
begin
raise_application_error (-20000, 'salary limit crossed - operation disallowed');
end;
/
Explanation : While the other things are same as explained in the above example, the text in red is the restricting "when" clause. Observe here that the when clause is the part of trigger header and its conditions are actually stored in the data dictionary. The phase of parsing on the insert and update statement decides whether or not to fire this trigger (if satisfies the conditions). The text in blue is the trigger action, which in this case is calling a built in procedure to fail the trigger and hence the conjugating DML. The error numbers allowed here are -20000 through -20999. The second argument is the error message to be passed on to the user.
Conclusion -
So here we had two very simple examples of the triggers to implement such constraints which may not have been possible with the integrity constraints. The most important limitation of the triggers however is that, they do not check the data already residing in the tables. The data validation for such data may have to be separately done, if the trigger was disabled for some time, or was created after the table was populated with the data.
Data integrity and DML restrictions, such which may not be possible with integrity constraints or triggers, may be implemented through views. Click Here to continue reading about the views.
That was rather a highly stuffed primer on the triggers and by no means can substitute a complete explanation, but the same has only been provided for the base, so that users will not feel the forthcoming discussion as alien. A dedicated post to triggers may sometime come in near future but meanwhile you may refer to this link.
Trigger Examples - Implementation of Data Integrity -
Example 1 - In the first example we shall see the implementation of a constraint, where we want that the data in the "dname" and "location" columns of "department" table should be always in upper case. Whereas we could simply put a check constraint, the problem with constraint would be that, it will fail the update or insert statement if the data is not provided in upper case by the user, thus consume time and resources in rejection and users then resorting to re-execute the operation. Whereas now, in the trigger, we implicitly convert the case of the data provided by the user and so trigger will ensure that the data which makes it to the columns is always uppercase, no matter in what case it was provided by the user. So the example -
create or replace trigger upper_case_dept_trig
before insert or update of dname, location on department
for each row
begin
:new.dname := upper(:new.dname);
:new.location := upper(:new.location);
end;
/Explanation : The code in red is triggering statement - specifies the timing for the trigger "before" in this case and events which are "insert" and "update" (with column list, in absence of such column list it will fire for all updates on any column(s)) and table name "department". In blue is the clause which tells that this is "row level" trigger, since we require to manipulate the data provided by the user, we are using "before" timed and its reference may be only available from :NEW, we are using a row level trigger. The code in grey is the trigger action - in this case converting the user provided data into upper case (upper is a function) and storing it in same vars so ensuring that it will always be upper case.
Example 2 - In the second example we ensure that the salary of any employee may not be greater than limit of 5000 only in department number 30. Remember that, if any employee is having salary greater than 5000 in department number 30, before the trigger is written or some user makes it so when the trigger is in the disabled state, then the trigger CAN NOT detect this, unlike the integrity constraint. However we may not easily apply this rule using integrity constraint. So the example -
create or replace trigger limitsal
before insert or update of deptno, salary on employee
for each row
when (
(new.deptno = 30 and new.salary > 5000) or
(new.deptno = 30 and old.salary > 5000) or
(old.deptno = 30 and new.salary > 5000)
)
begin
raise_application_error (-20000, 'salary limit crossed - operation disallowed');
end;
/
Explanation : While the other things are same as explained in the above example, the text in red is the restricting "when" clause. Observe here that the when clause is the part of trigger header and its conditions are actually stored in the data dictionary. The phase of parsing on the insert and update statement decides whether or not to fire this trigger (if satisfies the conditions). The text in blue is the trigger action, which in this case is calling a built in procedure to fail the trigger and hence the conjugating DML. The error numbers allowed here are -20000 through -20999. The second argument is the error message to be passed on to the user.
Conclusion -
So here we had two very simple examples of the triggers to implement such constraints which may not have been possible with the integrity constraints. The most important limitation of the triggers however is that, they do not check the data already residing in the tables. The data validation for such data may have to be separately done, if the trigger was disabled for some time, or was created after the table was populated with the data.
Data integrity and DML restrictions, such which may not be possible with integrity constraints or triggers, may be implemented through views. Click Here to continue reading about the views.
No comments:
Post a Comment