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.
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
raise_application_error (-20000, 'salary limit crossed - operation disallowed');
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.
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.