Breaking News: Grepper is joining You.com. Read the official announcement!

create a sample complete and comprehensive oracle trigger in sql code

Good Gecko answered on July 21, 2023 Popularity 1/10 Helpfulness 1/10

answer create a sample complete and comprehensive oracle trigger in sql code

create a sample complete and comprehensive oracle trigger in sql code

Comment

-- Assuming the following tables exist:
-- employees (employee_id, first_name, last_name, hire_date)
-- audit_log (audit_id, action_type, action_timestamp, user_id, affected_table, affected_row_id, old_values, new_values)

-- Create the trigger
CREATE OR REPLACE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action_type VARCHAR2(10);
    v_old_values  VARCHAR2(1000);
    v_new_values  VARCHAR2(1000);
BEGIN
    -- Determine the action type (INSERT, UPDATE, DELETE)
    IF INSERTING THEN
        v_action_type := 'INSERT';
    ELSIF UPDATING THEN
        v_action_type := 'UPDATE';
    ELSIF DELETING THEN
        v_action_type := 'DELETE';
    END IF;

    -- Set the old and new values for UPDATE and DELETE actions
    IF UPDATING OR DELETING THEN
        v_old_values := 'Old: ' ||
            'Employee_ID=' || :old.employee_id || ', ' ||
            'First_Name=' || :old.first_name || ', ' ||
            'Last_Name=' || :old.last_name || ', ' ||
            'Hire_Date=' || TO_CHAR(:old.hire_date, 'YYYY-MM-DD');
    END IF;

    IF UPDATING OR INSERTING THEN
        v_new_values := 'New: ' ||
            'Employee_ID=' || :new.employee_id || ', ' ||
            'First_Name=' || :new.first_name || ', ' ||
            'Last_Name=' || :new.last_name || ', ' ||
            'Hire_Date=' || TO_CHAR(:new.hire_date, 'YYYY-MM-DD');
    END IF;

    -- Insert the audit record into audit_log table
    INSERT INTO audit_log (audit_id, action_type, action_timestamp, user_id, affected_table, affected_row_id, old_values, new_values)
    VALUES (audit_log_seq.nextval, v_action_type, SYSTIMESTAMP, USER, 'employees', :new.employee_id, v_old_values, v_new_values);
END;
/

Popularity 1/10 Helpfulness 1/10 Language sql

Source: chat.openai.com

Tags: oracle sql

Link to this answer
Share Copy Link

Contributed on Jul 21 2023

Good Gecko

0 Answers Avg Quality 2/10

create a sample complete and comprehensive oracle trigger in sql code

Contents

More Related Answers

create a sample complete and comprehensive oracle trigger in sql code

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.