Scribd
Upload
5 views4 pages

DBMS Ex7

The document outlines the creation of SQL triggers for managing insert, delete, and update operations in database tables. It includes examples of triggers to enforce age restrictions on sailor entries, maintain minimum stock levels, and log deletions from an employee table. The provided SQL code demonstrates the implementation of these triggers and their successful execution.

Uploaded by

isithore.27csa
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
5 views4 pages

DBMS Ex7

The document outlines the creation of SQL triggers for managing insert, delete, and update operations in database tables. It includes examples of triggers to enforce age restrictions on sailor entries, maintain minimum stock levels, and log deletions from an employee table. The provided SQL code demonstrates the implementation of these triggers and their successful execution.

Uploaded by

isithore.27csa
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

7) SQL TRIGGERS FOR INSERT, DELETE, AND UPDATE OPERATIONS

AIM:
To create Triggers for Insert, Delete, and Update Operations.
QUERIES:
1)Create table sailor(sid,sname,age). Add trigger before insert to allow age greater than 18
SQL> CREATE TABLE SAILOR(SID INT PRIMARY KEY, SNAME VARCHAR(30),AGE INT);
Table created.
CODE FOR TRIGGER:
CREATE OR REPLACE TRIGGER check_age_before_insert
BEFORE INSERT ON SAILOR
FOR EACH ROW
BEGIN
IF :NEW.AGE <= 18 THEN (OR) WHEN (NEW.AGE <= 18)
RAISE_APPLICATION_ERROR(-20001, 'Age must be greater than 18.');
END IF;
END;
/
SQL> @"C:\Users\A12-31\Desktop\plsql1.sql";
Trigger created.
SQL> INSERT INTO SAILOR VALUES(1,'JOHN',20);
1 row created.
SQL> INSERT INTO SAILOR VALUES(2,'MIKE',19);
1 row created.
SQL> INSERT INTO SAILOR VALUES(6,'RICHE',10);

2)Create table stock(pid ,stock). Add trigger before update to maintain a stock above 5 on
update.
SQL> CREATE TABLE STOCK(PID INT PRIMARY KEY, STOCK INT);
Table created.
CODE FOR TRIGGER:
CREATE OR REPLACE TRIGGER maintain_min_stock
BEFORE UPDATE ON STOCK
FOR EACH ROW
BEGIN
IF :NEW.STOCK < 5 THEN
RAISE_APPLICATION_ERROR(-20002, 'Stock must be at least 5.');
END IF;
END;
/
SQL> @"C:\Users\A12-31\Desktop\plsql2.sql";
Trigger created.
SQL> INSERT INTO STOCK VALUES(101,10);
1 row created.
SQL> INSERT INTO STOCK VALUES(102,7);
1 row created.
SQL> UPDATE STOCK SET STOCK=6 WHERE PID=101;
1 row updated.
SQL> UPDATE STOCK SET STOCK=3 WHERE PID=102;

3)Write a Trigger to carry out the following action: on deleting any records from the emp
table ,the same values must be inserted into the log table.
SQL> CREATE TABLE EMPL(EMP_ID INT PRIMARY KEY,EMP_NAME
VARCHAR(20),SALARY DECIMAL(10,2));
Table created.
SQL>CREATE TABLE LOG(EMP_ID INT,EMP_NAME VARCHAR(20),SALARY
DECIMAL(10,2),DELETED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Table created.
CODE FOR TRIGGER:
CREATE OR REPLACE TRIGGER log_emp_deletions
AFTER DELETE ON EMPL
FOR EACH ROW
BEGIN
INSERT INTO LOG (emp_id, emp_name, salary, deleted_at)
VALUES (:OLD.EMP_ID, :OLD.EMP_NAME, :OLD.SALARY, SYSTIMESTAMP);
END;
/
SQL> @"C:\Users\A12-31\Desktop\plsql3.sql";
Trigger created.
SQL> INSERT INTO EMPL VALUES(201,'ALICE',50000);
1 row created.
SQL> INSERT INTO EMPL VALUES(202,'BOB',60000);
1 row created.
SQL> DELETE FROM EMPL WHERE EMP_ID=201;
1 row deleted.
SQL> SELECT * FROM EMPL;

SQL> SELECT * FROM LOG;

RESULT:
Thus, the queries are created and the output is verified.

You might also like