KEMBAR78
Triggers Examples Lab 6 2 | PDF
Scribd
Upload
37 views4 pages

Triggers Examples Lab 6 2

The document contains SQL trigger examples for managing employee salary and logging changes in a database. It includes triggers to set salary limits, log old values during updates, track deletions, and control salary changes exceeding 30%. Each trigger is defined to ensure data integrity and maintain a history of changes in the employee records.

Uploaded by

dias.ashimov.04
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
37 views4 pages

Triggers Examples Lab 6 2

The document contains SQL trigger examples for managing employee salary and logging changes in a database. It includes triggers to set salary limits, log old values during updates, track deletions, and control salary changes exceeding 30%. Each trigger is defined to ensure data integrity and maintain a history of changes in the employee records.

Uploaded by

dias.ashimov.04
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

EXAMPLE 1

CREATE OR REPLACE TRIGGER set_salary_limit


BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Check if salary is NULL or 0, set to minimum value
IF :NEW.salary IS NULL OR :NEW.salary = 0 THEN
:NEW.salary := 3000; -- Minimum salary
END IF;

-- Check if salary exceeds maximum value, set to maximum value


IF :NEW.salary > 20000 THEN
:NEW.salary := 20000; -- Maximum salary
END IF;
END;
/
EXAMPLE 2
CREATE TABLE change_log (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
column_name VARCHAR2(50),
old_value VARCHAR2(100),
change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TRIGGER log_old_values


BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO change_log (column_name, old_value)
VALUES ('salary', TO_CHAR(:OLD.salary));

INSERT INTO change_log (column_name, old_value)


VALUES ('job_id', :OLD.job_id);

INSERT INTO change_log (column_name, old_value)


VALUES ('manager_id', TO_CHAR(:OLD.manager_id));
END;
/
EXAMPLE 3

CREATE TABLE deletion_log (


id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
deleted_row_data VARCHAR2(500),
deletion_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE TRIGGER track_deletions


AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO deletion_log (deleted_row_data)
VALUES (
'Employee ID: ' || :OLD.employee_id || ', Name: ' || :OLD.first_name || ' '
|| :OLD.last_name ||
', Job ID: ' || :OLD.job_id || ', Salary: ' || :OLD.salary
);
END;
/
EXAMPLE 4
CREATE OR REPLACE TRIGGER control_salary_change
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
-- Check if the salary change exceeds 30%
IF ABS(:NEW.salary - :OLD.salary) > :OLD.salary * 0.3 THEN
RAISE_APPLICATION_ERROR(
-20005,
'Salary change exceeds the allowed limit of 30%'
);
END IF;
END;
/

You might also like