Vishwanath Dalvi is a gifted engineer and tech enthusiast. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created. I am running into compilation issues with my trigger and am having trouble understanding how to create a trigger that references a sequence and another table, and insert those values into a log table that I've created.I have read that using SERIAL not null on the Table column entry may eliminates the need for a sequence and referencing table, but I need that for my homework to work.CREATE OR REPLACE TRIGGER my_test_trg BEFORE INSERT OR UPDATE OR DELETE ON my_table FOR EACH ROW BEGIN -- Flags are booleans and can be used in any branching construct. If you need some code to perform an operation that needs to commit, regardless of the current transaction, you should put it in a stored procedure defined as an autonomous transaction, shown here.
CASE WHEN INSERTING THEN -- Include any code specific for when the trigger is fired from an INSERT. WHEN UPDATING THEN -- Include any code specific for when the trigger is fired from an UPDATE. WHEN DELETING THEN -- Include any code specific for when the trigger is fired from a DELETE. DML triggers have four basic timing points for a single table. With the exception of Compound Triggers, the triggers for the individual timing points are self contained and can't automatically share state or variable information.
The workaround for this is to use variables defined in packages to store information that must be in scope for all timing points.
The following code demonstrates the order in which the timing points are fired.
CREATE or REPLACE TRIGGER test001 AFTER INSERT OR UPDATE OR DELETE ON tabletest001 REFERENCING OLD AS old_buffer NEW AS new_buffer FOR EACH ROW WHEN (new_buffer.field1 = 'HBP00' OR old_buffer.field1 = 'HBP00') DECLARE Operation NUMBER; Customer Code CHAR(10 BYTE); BEGIN IF DELETING THEN Operation := 3; Customer Code := :old_buffer.field1; END IF; IF INSERTING THEN Operation := 1; Customer Code := :new_buffer.field1; END IF; IF UPDATING THEN Operation := 2; Customer Code := :new_buffer.field1; END IF; // DO SOMETHING ...
A database trigger is a stored procedure that automatically executes whenever an event occurs. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.
And of course the Oracle 11g Reference Doc for Triggers, I've gone over it but you can check it out yourself too. Each time a pledge payment is added, changed, or removed, the following information should be captured in a separate table: username, current date, action taken (INSERT, UPDATE, or DELETE), and the idpay value for the payment record.