Lecture Notes Unit5 chapter16 Trigger Creation

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : Trigger
Sub-Topic :
Definition of trigger, Difference between trigger and procedure, Advantages and disadvantages of trigger, Components of a trigger, Types of trigger, Table Trigger (Row level or statement level), Instead of trigger, system event trigger, user event trigger, Syntax for creating a trigger, Modifying a trigger, Deleting a trigger, Reference to column inside trigger, validating data entry, replicating with trigger - insertion, replicating trigger with deletion, obtaining information about a trigger



Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
Unit V
Chapter 15 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter-15-pl-sql-programming/270383949

Unit IV

Chapter 14 Synonym : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter14_synonyms-pdf/270327685
Chapter 13 Users, Privileges : https://www.slideshare.net/slideshow/lecture-notes-unit4-chapter13-users-roles-and-privileges/270304806
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800

About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purposes only. The content presented here reflects the author’s understanding in the field of RDBMS as of 2024.

1. RDBMS -Unit V
Chapter 16
TRIGGER
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
LEARN ORACLE 8i, JOSE A RAMALHO

2. TRIGGER -Definition
➢Triggersareroutinesorproceduresthatareusedwhen
anINSERT,UPDATE,orDELETEcommandis
executedinatableoraview..
DifferencebetweenTriggerandProcedure
➢Atriggerisautomaticallyexecutedwithoutanyaction
requiredbytheuser.
➢Astoredprocedure,ontheotherhand,needstobe
explicitlyinvoked.

3. TRIGGER –Merits and Demerits
AdvantagesofTrigger
➢Themajorapplicationofatriggeristocreate
consistenciestothedatabase.
➢Createvalidationmechanisms.
Restrictions
➢AtriggercannotexecutetheCOMMIT,ROLLBACK,
orSAVEPOINTcommands.
➢Italsocannotcallproceduresorfunctions

4. TRIGGER –Structure

5. Components of a Trigger
Atriggerhasthreeparts:
1.SQLcommandthatactivatesthetrigger(triggering
event)
ThetriggercanbeactivatedbyaSQLcommandorbya
userevent
2.Triggerrestriction
RepresentedbytheWHENclause,itspecifieswhat
conditionmustbetrueforthetriggertobetriggered.
3.Triggeraction
ThePL/SQLblock,orJavaorCroutineexecutedbythe
trigger.

6. Types of Triggers
Oracle8ihasfourtypesoftriggers,dependingonthe
application:
➢TabletriggersappliedtoDMLcommands.
➢INSTEADOFtriggersappliedtoviews.
➢SYSTEMEVENTtriggersappliedtothedatabasein
thestartup,shutdown,anderrorhandlingoperations.
➢USEREVENTtriggersappliedtoauserorobject
schema.

7. Types of Triggers -Table Triggers
➢Therearetwodistincttypesoftriggersthatcanbe
usedinatable.
➢Thefirsttype,statement-levelandtheothertype,row-
level.
➢Statement-leveltriggersaretriggeredonlyonce.For
example,whenanUPDATEcommandupdates15
rows,thecommandscontainedinthetriggerare
executedonlyonce,andnotwitheveryprocessedrow.
➢row-leveltriggersareexecutedonalltherowsthat
areaffectedbythecommandthatenabledthetrigger.

8. Types of Triggers –Instead of Triggers
➢TheINSTEADOFtriggerisavailableonlyinthe
EnterpriseversionofOracle.

9. Types of Triggers –System Event and User Event Triggers
➢AtriggeriscreatedwiththeCREATETRIGGER
command.Ithasthefollowingsyntax:

10. Modifying a Trigger -Enabling/Disabling a Trigger
➢Whenatriggeriscreated,itisautomaticallyenabled
andistriggeredwheneverthetriggeringcommandand
theexecutioncommandistrue.
➢Todisabletheexecutionofthetrigger,usetheALTER
TRIGGERcommandwiththeDISABLEclause.To
enableitagain,usetheENABLEclause:

11. Deleting a Trigger
➢Todeleteatrigger,usetheDROPTRIGGER
command:
Syntax:
DROPTRIGGERname_of_trigger;
➢Thisremovesthetriggerstructurefromthedatabase

12. References to Columns Inside a Trigger
➢Youcanaccessthevalueofacolumninsidearow-
leveltrigger.
➢ForanINSERTcommand,thevaluesofthefieldsthat
willbewrittenmustbeprecededby:new.
➢ForaDELETEcommand,thevaluesoftherowfields
thatarebeingprocessedmustbeprecededby:old.
➢ForanUPDATEcommand,theoriginalvaluethatis
beingwrittenisaccessedwith:old.Thenewvalues
thatwillbewrittenmustbeprecededby:new.

13. Validating the Data Entry
➢createtableemp1(namevarchar(20),salary
number(7),enonumber(5));
➢CREATEORREPLACETRIGGERTESTASAL
BEFOREINSERTORUPDATEOFSALARYON
SYSTEM.EMP1FOREACHROW
BEGIN
IF:NEW.SALARY>8000THEN
RAISE_APPLICATION_ERROR( -
20000,'INCORRECTVALUE');
ENDIF;
END;
/
➢Executetheprogramandtrytoinsertvaluesbelow
8000andabove8000.seetheresult

14. Validating the Data Entry
➢Here,ifthesalaryisbelow8000acceptedotherwise
thetriggerdisplaystheerrormesssage.

15. Replicating with Triggers
➢Triggersareanefficientmechanismtoreplicatedata
amongtables.
➢Wecaneasilycreatetriggersthatinsert,delete,or
modifyanothertablebasedontheoperations
performedinthecurrenttable.

16. Replicating with Triggers -insertion
1.createtableemp3(namevarchar(20),salary
number(7),enonumber(5));
2.CREATETRIGGERREP_INS
AFTERINSERTONEMP1
FOREACHROW
BEGIN
INSERTINTOEMP3VALUES(:NEW.name,
:NEW.salary,:NEW.eno);
END;
3.Executethetrigger
4.insertintoemp1values('ramesh',5000,105);
(Whateverthechangesmadeinemp1,itwillreflectto
emp2.)
5.Select*fromemp3;

17. Replicating with Triggers -Deletion
1.Createthetrigger
CREATETRIGGERREP_DEL
BEFOREDELETEONEMP1
FOREACHROW
BEGIN
DELETEFROMEMP3
WHEREeno=:OLD.eno;
END;
/
2.Deletetherecordfromemp1.
Deletefromemp1whereeno=105.
3.Checktheemp3table,theeno=105alsodeleted.
Select*fromemp3;

18. Obtaining Information about a Trigger
➢To view the DBA_TRIGGERS. (using
System/manager )
SQL> descdba_triggers;
➢To view the user’s triggers, you use the view
USER_TRIGGERS:
SQL> describe user_triggers;
➢To view the trigger name and body of the trigger
SQL> select trigger_name,trigger_bodyfrom
user_triggerswhere trigger_name=’REP_INS’;