1 / 15

IT420: Database Management and Organization

IT420: Database Management and Organization. Triggers and Stored Procedures 24 February 2006 Adina Cr ă iniceanu www.cs.usna.edu/~adina. Last time. SQL Views Triggers. Today. More triggers Stored procedures. Triggers.

fulfordc
Télécharger la présentation

IT420: Database Management and Organization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT420: Database Management and Organization Triggers and Stored Procedures 24 February 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

  2. Last time • SQL Views • Triggers

  3. Today • More triggers • Stored procedures

  4. Triggers • Trigger: stored program that is executed by the DBMS whenever a specified event occurs • Associated with a table or view • Three trigger types: BEFORE, INSTEAD OF, and AFTER • Each type can be declared for INSERT, UPDATE, and/or DELETE • Resulting in a total of nine trigger types

  5. Firing Triggers • When a trigger is fired, the DBMS supplies: • Old and new values for the update • New values for inserts • Old values for deletions • The way the values are supplied depends on the DBMS product • Trigger applications: • Provide default values • Enforce data constraints • Update views • Perform referential integrity actions

  6. Create Trigger • CREATE TRIGGER trigger_name ON table_or_view_name AFTER | BEFORE | INSTEAD OF INSERT | UPDATE | DELETE AS trigger_code

  7. Trigger for Enforcing a Data Constraint Arenas (ArenaID, ArenaName, City, ArenaCapacity), ArenaCapacity >= 5000 CREATE TRIGGER minseating ON Arenas /*trigger associated to Arenas*/ FOR INSERT /*executed after an insert*/ AS DECLARE @capacity as int /*variable declarations */ SELECT @capacity = ArenaCapacity /* get values inserted */ FROM inserted if @capacity < 5000 BEGIN ROLLBACK /*undo the insert*/ Print 'Arena too small‘ /*message for the user*/ END

  8. Class Exercise • Concerts (PerformerID, ArenaID, ConcertDate, TicketPrice) • Define a trigger: if inserted price is below 25, print a message and change the ticket price to 25. • Insert rows to test the trigger

  9. Stored Procedures • A stored procedure is a program that is stored within the database and is compiled when used • In Oracle, it can be written in PL/SQL or Java • In SQL Server, it can be written in TRANSACT-SQL • Stored procedures can receive input parameters and they can return results • Stored procedures can be called from: • Programs written in standard languages, e.g., Java, C# • Scripting languages, e.g., PHP, JavaScript, VBScript • SQL command prompt, e.g., SQL*Plus, Query Analyzer

  10. Stored Procedure Advantages • Greater security as store procedures are always stored on the database server • SQL can be optimized by the DBMS compiler • Code sharing resulting in: • Less work • Standardized processing • Specialization among developers

  11. Create And Execute Stored Procedures • CREATE PROCEDURE proc_name AS proc_code • exec proc_name [@param1 = ]value1, …

  12. Stored Procedure Example • Performers (PerformerID, PerformerName, Street, City, State, Zip, ActivityID) • Procedure: Insert a performer only if same name and zip not already in the table

  13. CREATE PROCEDURE performer_Insert @ID int, @NewName char(20), @street char(20), @city char(15), @state char(2), @NewZip int, @activityID int AS DECLARE @Count as int SELECT @Count = Count(*) FROM Performers WHERE PerformerName =@NewName AND Zip = @NewZip IF @Count > 0 BEGIN PRINT 'Performer is already in the Database' RETURN END BEGIN TRANSACTION INSERT INTO Performers VALUES (@ID, @NewName, @street, @city, @state, @NewZip, @activityID) PRINT 'Performer added to database' COMMIT Performers (PerformerID, PerformerName, Street, City, State, Zip, ActivityID)

  14. Class Exercise • Add code to the previous procedure to prevent anyone with a name like ‘Spears’ to be inserted into the DB. Print an error explicative message when that happens. • Test the procedure (exec ….)

  15. Triggers vs. Stored Procedures

More Related