What can a ‘database trigger’ be used for? Explain concisely what the code below does.

Question

What will be an ideal response?

Answer 1

CREATE TRIGGER st_customer_trg BEFORE INSERT OR UPDATE ON st_customer
FOR EACH ROW
BEGIN
IF :old.customer_insert_user IS NULL THEN
:new.customer_insert_user := USER;
:new.customer_insert_date := SYSDATE;
:new.customer_update_user := NULL;
:new.customer_update_date := NULL;
ELSE
:new.customer_insert_user := :old.customer_insert_user;
:new.customer_insert_date := :old.customer_insert_date;
:new.customer_update_user := USER;
:new.customer_update_date := SYSDATE;
END IF;
END;

A database trigger is a stored procedure that Oracle invokes ("fires") automatically when certain events occur, for example, when a DML operation modifies a certain table. Triggers enforce business rules, prevent incorrect values from being stored, and reduce the need to perform checking and cleanup operations in each application. The above trigger fires before insert or update on st_customer. If the field customer_insert_user of the row in question is empty, USER, SYSDATE etc are inserted, otherwise original data is kept.