Ex.
No: 4 Application Development - PART II
Aim:
To implement trigger concept in student database in insertion, updation and deletion
Triggers
BEFORE INSERT:
CREATE TRIGGER bit
BEFORE INSERT ON StudentMaster1
FOR EACH ROW
BEGIN
IF :NEW.yrofadmision==2021 THEN
dbms_output.put_line('Admit');
ELSE
dbms_output.put_line('DON T ADMIT');
END IF;
END;
/
INSERT INTO StudentMaster_1 VALUES ('yeager',107,'Male','2023',400,8,'3','C');
Output
BEFORE UPDATE:
create trigger bupd
before update on ResultMaster_1
for each row
begin
IF :NEW.result=’pass’ then
dbms_output.put_line('u r eligible');
ELSE
raise_application_error(-20000,’u r nor eligible');
END IF;
END;
/
update ResultMaster_1 set result =’pass’ where studname=’tamil’;
Output
BEFORE DELETE
CREATE TRIGGER bdel
BEFORE DELETE ON ResultMaster_1
FOR EACH ROW
BEGIN
IF :OLD.dept = 'Science' THEN
dbms_output.Put_line('Deletion Permitted');
ELSE
Raise_application_error(20001,'Deletion Restricted');
END IF;
END;
/
Output
After Insert
CREATE OR REPLACE TRIGGER gen
AFTER INSERT ON StudentMaster
FOR EACH ROW
BEGIN
IF :new.gender= 'Male' THEN
dbms_output.put_line('Student is male');
ELSE
dbms_output.put_line('Student is female');
END IF;
END;
INSERT INTO StudentMaster_1 VALUES ('mikasa','118','Female','2023',400,8,'3','C');
Output
After update
CREATE TRIGGER bu3
AFTER UPDATE
ON studentmaster
FOR EACH ROW
WHEN (NEW.regno>0)
DECLARE
diff number;
BEGIN
diff:=:NEW.markspersem-:OLD.markspersem;
dbms_output.put_line('Difference:'||diff);
END;
update studentmaster set markspersem='250' where regno='104'
Output
After delete
CREATE TRIGGER d1
AFTER DELETE
ON employee
BEGIN
dbms_output.put_line('Trigger is activated');
END;
DELETE FROM employee WHERE estreet='secondstreet';
Output
QUERIES:
1. Write a code in PL/SQL to create a trigger that restricts the insertion of new rows if the
total of column value exceed a certain threshold.
CREATE TRIGGER binsert
BEFORE INSERT
ON studentmaster1
FOR EACH ROW
DECLARE
count1 int(10);
BEGIN
select count(studname) into count1 from studentmaster1;
IF count1 > 5 THEN
RAISE_APPLICATION_ERROR(-20001,'YOU ARE EXCEEDING');
END IF;
END;
OUTPUT:
insert into StudentMaster1 values('sara',218,'Female',2023,76,5,2023,'A')
2. Write a code in PL/SQL to implement a trigger that automatically calculates and
updates a total column for a table whenever a new rows are inserted
CREATE TRIGGER u
BEFORE INSERT
ON Employeemaster
FOR EACH ROW
DECLARE
total_rows NUMBER;
BEGIN
SELECT COUNT(*) INTO total_rows FROM Employeemaster;
dbms_output.put_line('New rows added. Total rows: ' || TO_CHAR(total_rows + 1));
END;
/
insert into employeemaster3 values(1001,'yeager','29-feb-
2000','maths','regular',21,'shioganshina',9734543,'b-ve');
OUTPUT:
3. Write a pl/sql code to create a trigger that automatically updates the last modified time whenever a
new row is updated
CREATE OR REPLACE TRIGGER t
BEFORE UPDATE ON employeemaster3
FOR EACH ROW
DECLARE
last_modified TIMESTAMP;
BEGIN
last_modified := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('Last Modified Time: ' || TO_CHAR(last_modified, 'YYYY-MM-DD
HH24:MI:SS'));
END;
/
Output:
4. Write a code in pl/sql to create a trigger that check for duplicate values in a specific
column and raises an exception if found
CREATE OR REPLACE TRIGGER c
BEFORE INSERT OR UPDATE ON employeemaster
FOR EACH ROW
DECLARE
duplicate_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO duplicate_count
FROM employeemaster
WHERE type= :NEW.type;
IF duplicate_count > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Duplicate values not allowed in the specified
column.');
END IF;
END;
/
Output:
5. Write a code in pl/sql to create a trigger that prevents update on a certain column during
specific hour of the day
CREATE OR REPLACE TRIGGER p
BEFORE UPDATE ON employeemaster
FOR EACH ROW
DECLARE
C_h NUMBER;
BEGIN
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) INTO C_h FROM DUAL;
IF UPDATING('type') AND current_hour BETWEEN 6 AND 17 THEN
RAISE_APPLICATION_ERROR(-20002, 'Updates to the specified column are not allowed during
specific hours.');
END IF;
END;
/
Output:
