We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.
CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON employees BEGIN IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18') THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502,'You may delete from EMPLOYEES table only during business hours.'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20500,'You may insert into EMPLOYEES table only during business hours.'); ELSIF UPDATING ('SALARY') THEN RAISE_APPLICATION_ERROR (-20503,'You may update SALARY only during business hours.'); ELSE RAISE_APPLICATION_ERROR (-20504,'You may update EMPLOYEES table only during normal hours.'); END IF; END IF; END; The OLD and NEW qualifiers can be used only with row triggers. The OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL and PL/SQL statement except when they are referenced in a WHEN restricting clause.
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.
1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
However, when a view becomes more complex it may lose its “updateable-ness,” and the Oracle INSTEAD-OF trigger must be used.
CREATE OR REPLACE TRIGGER order_info_insert INSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=TRIGGER_NAME — Name of the trigger TRIGGER_TYPE — The type is BEFORE, AFTER, INSTEAD OF TRIGGER_EVENT — The DML operation firing the trigger TABLE_OWNER — Owner of the table BASE_OBJECT_TYPE — Object type TABLE_NAME — Name of the database table COLUMN_NAME – Name of the Column REFERENCING_NAMES — Name used for : OLD and : NEW WHEN_CLAUSE — The when_clause used STATUS — The status of the trigger DESCRIPTION — Description ACTION_TYPE — Type of action TRIGGER_BODY — The action to take Don’t use triggers – The best way to avoid the mutating table error is not to use triggers.