On this issue we will talk about the database triggers (TRIGGER). We will mention the basic subjects of the preparation and the concepts used for triggers. In order to test the triggers you will learn at this study, use your previous SQL experiences from F9 sql transactions in Çözüm Hospital Automation Software to form DENEME & LOGDENEME tables with sql commands.

           CREATE TABLE DENEME (KEY NUMBER,AD VARCHAR2(15),SOYAD VARCHAR2(15),CINSIYET VARCHAR2(5))

           CREATE SEQUENCE DENEMESEQ MINVALUE 1 INCREMENT BY 1 NOCACHE NOCYCLE

           CREATE TABLE LOGDENEME (LOGKEY NUMBER,KEY NUMBER,AD VARCHAR2(15),SOYAD VARCHAR2(15),CINSIYET VARCHAR2(5),ZAMAN DATE,AÇIKLAMA VARCHAR2(50))

           CREATE SEQUENCE LOGDENEMESEQ MINVALUE 1 INCREMENT BY 1 NOCACHE NOCYCLE

           Triggers are used for transactions; new record entry, delete, modify, to control the ordered process on a table.

           The regarding point on preparing the triggers is that, the rows of the trigger should not exceed sixty in number. That is because each time the trigger runs than the Oracle Database compiles. Forming Procedures for the transactions using these type of multi- row triggers will benefit your system performance as the Procedures are compiled just for once where the triggers are compiled each time they run.

           Forming the triggers:

           Create Trigger DenemeTrigger1

           is used to form the trigger. If the trigger is being formed previously;

           Create or Replace Trigger DenemeTrigger1

           can be used to form again without deleting the trigger. You may use the same namings with the tables on forming the triggers. But it is not suggested in order to prevent the various future disorders.

           You should determine when the trigger will run, after naming it. There are 2 alternatives for this. BEFORE and AFTER. These alternatives should be used just after the name enrollment.

           Create Trigger DenemeTrigger1

           After

           Triggers can be run with 3 different Sql statements. These are:

           1.         Insert
           2.         Update
           3.         Delete

           Triggers may run for one kind of a table or for all fields. If it is planned to run as a result of a process on some columns, OF key should be written after the selected statement and comma sign should be placed between columns names, to be used till the section where ON is written.

           Create Trigger DenemeTrigger1 After Update Of AD,SOYAD On Deneme

           in above example, the trigger will run, on any changes of Ad & Soyad fields on Deneme table.

           Note: A trigger can be used only for one table.

           The namings and running processes of the triggers are mentioned so far, lets clarify their running situations with related examples.

           1.Create Trigger DenemeTrigger1 After Delete On Deneme

           In  this case the trigger will run when a deletion process occurs on Deneme table

           2. Create Trigger DenemeTrigger1

           After Update Or Delete or Insert On Deneme

           The trigger above will run after the delete, update and new record process on the Deneme table.

           3. Create Trigger DenemeTrigger1

           Before Delete On Deneme

           This trigger will run before a deletion process from Deneme table.

           Trigger types should be determined, there are two types of triggers. These are;

           1.ROW TRIGGERS

           These run separately for each row that is affected by the trigger statement.

           2.STATEMENT TRIGGERS

           It doesn’t run for each of the affected row but just for once. The purpose of using here is to obtain a complex security control on the running time or user.

           Example: Create Trigger DenemeTrigger1 Before Delete On Deneme For Each Row

           REFERENCING statement is used to avoid the naming confusion within the table names. But it is not a preferred usage.

           Example:

           Create Trigger DenemeTrigger1 Before Delete On Deneme Referencing Deneme As H For Each Row

           WHEN statement is used in row triggers. It provides the trigger to be conditional, and run when the condition occurs. OLD.AD,:OLD.SOYAD,:OLD.CINSIYET,SYSDATE,'CİNSİYET DEĞİŞTİRİLDİ.); END;

          If the value of the CINSIYET column is changed following to the updates on the DENEME table, the information 'CİNSİYET DEĞİŞTİRİLDİ' is added to LOGDENEME tables explanation field.

           By WHEN statement   only the trigger content statements are run when the field CINSIYET is modified. After the forming of the trigger the sql commands to run the trigger is prepared. Prior to that lets add a record of gender 'ERKEK' to trail table.

           INSERT INTO DENEME (KEY,AD,SOYAD,CINSIYET) VALUES(DENEMESEQ.NEXTVAL,'KEVSER','GÜMÜŞ','ERKEK')

           Tetikleyicide UPDATE ifadesi kullanıldığı için, INSERT veya DELETE işlemleri sonucu tetikleyici çalışmaz, sadece UPDATE işlemi sonrası çalışır.

           Controlling the records. SELECT * FROM DENEME

           Change of value of the record in gender column.

           UPDATE DENEME SET CINSIYET = 'KADIN' WHERE KEY = 1

           This update command will run trigger and a record will be added to LOGDENEME table. Lets check LOGDENEME table.

SELECT * FROM LOGDENEME

           Note: Transaction control is inapplicable within the trigger. Therefore ROLLBACK and COMMIT commands cannot be used for the trigger.

           Disabling the triggers:

           By ALTER TRIGGER command triggers are enabled/disabled.

           Alter Trigger DenemeTrigger1 Disable;

           With this command DenemeTrigger1 trigger will be disabled. In order to enable a disabled trigger;

           Alter Trigger DenemeTrigger1 Enable;

           Command is used.

           The following SQL statement is used to disable the entire triggers belonging to a table.

           Alter Table Deneme Disable All Triggers;

          In order to run all the triggers on a test table;

           Alter Table Deneme Enable All Triggers ;

           commands are used.

           In order to take information about the triggers defined on the database; 
           1.USER_TRIGGERS 
           2.ALL_TRIGGERS
           3.DBA_TRIGGERS

           display tables are used.

           Select trigger_name, trigger_type, triggering_event, table_name from User_triggers where table_name = 'DENEME'

           Select trigger_name,trigger_type,triggering_event,table_name from All_triggers where table_name = 'DENEME'

           Select trigger_name,trigger_type,triggering_event,table_name from Dba_triggers

           DROP TRIGGER command is used to delete the created triggers.

           Drop Trigger DenemeTrigger1

           Note: Trigger may invite another on running. Therefore, this should be noticed when a trigger is prepared and should only be used when necessary.

           Example:Add a column of one character named TIP with Alter Table command to LogDeneme table.

           Alter Table LogDeneme ADD (TIP VARCHAR2(1))

           Prior to delete and update transactions on Deneme table, transfer process style to 'TIP' and hide the transaction style to LOGDENEME table.

CREATE OR REPLACE TRIGGER DenemeTrigger2 BEFORE UPDATE OR DELETE ON Deneme
FOR EACH ROW
DECLARE
TIP VARCHAR2(1);
BEGIN
IF DELETING THEN
TIP := 'D';
ELSE
TIP := 'U';
END IF;
INSERT INTO LOGDENEME
(LOGKEY,KEY,AD,SOYAD,CINSIYET,ZAMAN,ACIKLAMA,TIP)
VALUES (LOGDENEMESEQ.NEXTVAL,:OLD.KEY,:OLD.AD,:OLD.SOYAD,:OLD.CINSIYET,SYSDATE,'CİNSİYET DEĞİŞTİRİLDİ.',TIP);
END;

          Lets run the Sql’s that will run the trigger then control our LogDeneme table.

           INSERT INTO DENEME (KEY,AD,SOYAD,CINSIYET) VALUES (DENEMESEQ.NEXTVAL,'AHMET','ÖRNEK','KADIN')

          Later correct the added gender record and then delete.

           UPDATE DENEME SET CINSIYET='ERKEK' WHERE KEY=2

           DELETE FROM DENEME WHERE KEY=2

           Finally check the addition made by the trigger to the LOGDENEME table.