CODEWITHSUNDEEP
oracle-databaseplsqltriggers
user100000001
user100000001

Reputation: 35

PL/SQL Triggers in Oracle

enter image description here

I have the following tables shown in the image. I need to create a trigger that inserts data into an AUDIT table from the PAYMENT table including information about the payment like- who made the payment (pay_from), Oracle user who processed the payment, appointment id (apt_id) and patient who made the payment (patient_id).

From the image above, the PAYMENT table has a relationship with the APPOINTMENT table which in turn has a relationship with PATIENT table.

My question is, how can I add "patient_id" from the APPOINTMENT table into my AUDIT table using the trigger? This is my code: 

CREATE OR REPLACE TRIGGER PAYMENT_AUDIT_TRIGGER 
AFTER INSERT ON PAYMENT 
FOR EACH ROW 
BEGIN
INSERT
INTO AUDIT_LOG VALUES
(
 AUDIT_LOG_AUDIT_ID_SEQ.NEXTVAL,
 :new.PATIENT_ID,
 :new.APT_ID,
 USER,
 SYSDATE,
 :new.PAY_FROM
);
END;

Upvotes: 0

Views: 188

Answers (1)

TommCatt
TommCatt

Reputation: 5636

This is easy in Oracle because it has both before and after triggers. The difference between them is, as may well be guessed, when they execute -- just before the operation or after the operation.

A before trigger is useful for intercepting the input stream for more sophisticated data testing and/or modifying some of the data (such as generating the key value from a sequence in INSERT operations). By "just before", I mean that all defined integrity checking is performed first -- type checking, check constraints, referential constraints, etc. So you can know that the data has already passed all those checks.

An after trigger is useful for logging the operation that just occurred. If there is any error condition that prevents the operation from finishing, even though it may have passed the initial system checking and the additional checking that may have taken place by the before trigger, the after trigger will not execute. A handy feature of this trigger is that the row presented to it will be exactly the row that was inserted/updated/deleted. That means any generated key value will be there for the after INSERT trigger.

This description is for Oracle and other systems that have both types of triggers. SQL Server only has the after trigger although it has recently extended the instead of trigger to work with tables -- in effect, making it a before trigger, although there are differences with "normal" triggers so users should read the documentation to be aware of these differences.

Upvotes: 1

Related Questions