Programmable Objects Part 2 SQL Triggers Recap, Slides of Database Management Systems (DBMS)

Download Programmable Objects Part 2 SQL Triggers Recap and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Programmable Objects

Part 2

SQL Triggers

Recap

• Programmable Objects

• Variables

• Control Flow Constructs (If/Else, While Loop)

• Stored Procedures

DDL Triggers

• Create

• Alter

• Drop

• For/After (create/alter/drop)

• AFTER specifies that the DML trigger is fired only when

all operations specified in the triggering SQL statement

have executed successfully. All referential cascade

actions and constraint checks also must succeed before

this trigger fires.

• AFTER is the default when FOR is the only keyword

specified.

DML Trigger

• Insert

• Update

• Delete

• For/After ( insert, update, delete)

• Instead of ( insert, update, delete)

• Specifies that the DML trigger is executed instead

of the triggering SQL statement, therefore,

overriding the actions of the triggering

statements. INSTEAD OF cannot be specified for

DDL or logon triggers and DML.

Example of DDL Trigger

• Drop table student

DML Trigger Example 1

• Problem Statement: Create a DML trigger on

student table for insert event that performs

following actions:

• Whenever a new student is entered in the

student table the trigger checks the age of

newly entered student. If age is greater than

18 then row is inserted else message is display

record can not be inserted.

• Student(StdId, name,age, cgpa)

DML Trigger Example 2

• Problem Statement: Create a DML trigger on

student table after update event that

performs following actions:

• Whenever the student cgpa is updated. The

trigger display the old cgpa and new cgpa of

that particular student.

• Student(StdId, name,age, cgpa)

Solution 1

• Create Trigger StudentCgpa
• On Student
• After Update
• As
• Declare @oldcgpa float
• Declare @newcgpa float
• Declare @stdid int
• Select @oldcgpa = (select cgpa from deleted)
• Select @newcgpa = (select cgpa from inserted)
• Print ‘OldCgpa’
• Print @oldgpa
• Print ‘newCgpa’
• Print @newcgpa
• Go Update student Set cgpa = 3. Where stdid = 3

Example 3

• Problem Statement: Create a DML trigger on

student table instead of insert event that

performs following actions:

• Whenever a new student is entered in the

student table the trigger checks the age of

newly entered student. If age is greater than

18 then row is inserted else message is display

record can not be inserted.

• Student(StdId, name, age, cgpa)

solution

• Create trigger studentage
• On student
• Instead of insert
• As
• Declare @age = (select age from inserted)
• If(@age> 18)
• Insert into student
• Values (stdId, name, age,cgpa)
• Select i.stdId, i.name, i.age, i.cgpa
• From inserted as i
• Else
• Print ‘Record can not be inserted’
• Go Insert into student Values (1,’Ali’,19,3.5)

Relational schema

Student_id Student_name Student_address Department_id 1 Ali Abc 1 2 Omer Def 2 3 Usman Ghy 3 4 Humza Hju 4 Deparment_id Department_name 1 CSE 2 EE 3 Civil 4 Mech Student Department

Student_id Student_name Student_address Before_dept After_dept Student-update-record

Practice Problem

• Consider the following student database result sheet, in which

student marks assessment is recorded. On that table create a

trigger for insert event. The trigger will perform following actions:

• Whenever the student quiz, assignment and OHTs marks are

entered in the student table the trigger will first compute the

sessional marks automatically based on quiz, assignment and OHTs

marks and insert these sessional marks into the table for that

particular student.

• Database Result-Sheet(student-id, Name, Quiz, Assignment, OHT-

1, OHT-2, Sessionals)

• The triggering event is as follows:
• Insert into Database-Result-Sheet values (1, ‘Ali’, 7, 9, 13, 11,null)