Hi,
In today’s article I will explain how you can create a trigger and what you can use it for. A trigger is a procedure within the database which is automatically executed by the database when an event takes place. I will focus on table triggers and there you have three events: INSERT, UPDATE, DELETE. That means when a row is inserted an event takes places and if you have a trigger defined for this event it is executed. What do you need this for? You can use this for several things. For example you want to fill some columns based on other columns. Or you want to log something in another table.
In my following example I will create two tables. One with the data itself and some kind of logging table. If I insert a new row to the table I will also add a row to the logging table with a timestamp and the action performed.
First we need the two tables:
| CREATE TABLE TBL_TRIGGER_TEST( ROW_ID NUMBER, ROW_VALUE NUMBER, ROW_COMMENT VARCHAR2(1000) ); CREATE TABLE TBL_TRIGGER_TEST_LOG( ROW_ID NUMBER, ROW_VALUE NUMBER, ROW_COMMENT VARCHAR2(1000), ROW_TS DATE, ROW_ACTION VARCHAR2(1) ); |
The table TBL_TRIGGER_TEST is the main table. We have a ROW_ID, a ROW_VALUE and a ROW_COMMENT, just to have some columns. In the table TBL_TRIGGER_TEST_LOG we will log all actions on the main table. In the case of the insertion of a new record we will insert a copy of it with date & time to the logging table. In the ROW_ACTION column we will insert an I (= INSERT).
Now we need the trigger:
| CREATE OR REPLACE TRIGGER TRG_TEST_INS BEFORE INSERT ON TBL_TRIGGER_TEST FOR EACH ROW BEGIN INSERT INTO TBL_TRIGGER_TEST_LOG VALUES(:new.ROW_ID, :new.ROW_VALUE, :new.ROW_COMMENT, sysdate, ‚I‘); END; |
A trigger is created via the CREATE OR REPLACE TRIGGER statement. If a trigger with the same name already exists it is overwritten. The next important thing is BEFORE. That defines when the trigger should be executed before the DML operation takes place or after. In the case of after you just write AFTER instead of BEFORE. INSERT is the kind of DML event. You can choose between INSERT, DELETE and UPDATE. When you select UPDATE you can also specify a column. Then you defined with ON followed by the table name on what table this trigger should be implemented. The FOR EACH ROW option defines that the trigger should be executed for each row that is inserted and not only once for all rows within the transaction.
After BEGIN the code itself starts. We are just inserting a new into the logging table. The first three columns will be filled with the values that are to be inserted in the main table. You can access them via :new. This is something like a pseudo row. Beside :new you can use :old to access the old values. That is important when you delete a row or update it.
You are not allowed to put a COMMIT in a trigger. The trigger is commited by the transaction that inserted a row in the main table. That’s why it is not needed here.
Now you can test the trigger. Just insert a new row to the main table and commit it. Afterwards you can show all rows from the logging table. You should find an exact copy of the row you’ve inserted to the main table and the timestamp should match the date/time when you’ve inserted it.
As a task you can try to implement a trigger for the delete event. When a row is deleted you want a copy in the logging table. The action flag should contain a ‘D’. The examples and the result to this task can be found in the download area. Have fun!
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!
