1 / 30

IT420: Database Management and Organization

Learn about SQL views, triggers, and stored procedures and how they can be used for security, data manipulation, and automation in database management.

gprescott
Download Presentation

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 SQL Views, Triggers and Stored Procedures 17 February 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

  2. Last time • SQL Views Kroenke, Database Processing

  3. Today • Updates on views • Triggers • Stored procedures Kroenke, Database Processing

  4. SQL Views • SQL view is a virtual table that is constructed from other tables or views • It has no data of its own, but obtains data from tables or other views • It only has a definition Kroenke, Database Processing

  5. CREATE VIEW Command • CREATE VIEW command: CREATE VIEW view_name AS select_statement • Use the view: • In SELECT statements • Sometimes in INSERT statements • Sometimes in UPDATE statements • Sometimes in DELETE statements Kroenke, Database Processing

  6. Uses for SQL Views • Security: hide columns and rows • Display results of computations • Hide complicated SQL syntax • Provide a level of isolation between actual data and the user’s view of data • three-tier architecture • Assign different processing permissions to different views on same table • Assign different triggers to different views on same table Kroenke, Database Processing

  7. Using Views Customers • Customer(CustID, CustName, Address, Phone) • CREATE VIEW CustomerV AS SELECT * FROM Customers • SELECT * FROM CustomerV SELECT * FROM CustomerV query result: Kroenke, Database Processing

  8. Using Views Customers • CREATE VIEW CustomerV AS SELECT * FROM Customers • SELECT * FROM CustomerV WHERE Address LIKE ‘%Annapolis%’ SELECT query result: Kroenke, Database Processing

  9. UPDATE on Views Customers table before update: • CREATE VIEW CustomerV AS SELECT * FROM Customers • UPDATE CustomerV SET Phone = ‘410-123-1234’ WHERE CustID = 01 Customers table after update: UPDATE impacts the Customers table Kroenke, Database Processing

  10. INSERT on Views Customers table • CREATE VIEW CustomerV AS SELECT * FROM Customers • INSERT INTO CustomerV VALUES(‘08’,’Scott White’,’DC’,’401-456-3415’) Customers table after insert: INSERT impacts the Customers table Kroenke, Database Processing

  11. DELETE on Views Customers table • CREATE VIEW CustomerV AS SELECT * FROM Customers • DELETE FROM CustomerV WHERE Address LIKE ‘%Annapolis%’ Customers table after delete: DELETE impacts the Customers table Kroenke, Database Processing

  12. Using Views – Case 2 Customers • Customer(CustID, CustName, Address, Phone) • CREATE VIEW CustomerV2 AS SELECT CustID, CustName, Phone FROM Customers • SELECT * FROM CustomerV2 SELECT * FROM CustomerV2 query result: Kroenke, Database Processing

  13. INSERT on Views - Case 2 Customers table • CREATE VIEW CustomerV2 AS SELECT CustID, CustName, Phone FROM Customers • INSERT INTO CustomerV2 VALUES(‘08’,’Scott White’,’401-456-3415’) Customers table after insert: Address NOT NULL, INSERT fails Kroenke, Database Processing

  14. Views – Case 3 Rental • Rental(RentalID, CustID, PlaneID, NbHours, HRate) • CREATE VIEW RentalView AS SELECT RentalID, CustID, PlaneID, NbHours*HRate AS Charge FROM Rental • SELECT * FROM RentalView SELECT * FROM RentalView query result: Kroenke, Database Processing

  15. INSERT on Views – Case 3 Rental • CREATE VIEW RentalView AS SELECT RentalID, CustID, PlaneID, NbHours*HRate AS Charge FROM Rental • INSERT INTO RentalView VALUES (03,113,01,1250) INSERT fails! UPDATE Charge fails! SELECT * FROM RentalView query result: Kroenke, Database Processing

  16. Updateable Views • Views based on a single table • No computed columns • All non-null columns present in view • Views with INSTEAD OF triggers defined on them • Views based on a single table, primary key in view, some non-null columns missing from view • Updates for non-computed columns ok • Deletes ok • Inserts not ok Kroenke, Database Processing

  17. 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 DELETE • Resulting in a total of nine trigger types Kroenke, Database Processing

  18. Programming Languages for Triggers • Depends on DBMS • Java or PL/SQL for Oracle • T-SQL for SQL Server • C++, C#, Visual Basic .NET for SQL Server 2005 Kroenke, Database Processing

  19. 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 Kroenke, Database Processing

  20. Create trigger • CREATE TRIGGER trigger_name ON table_or_view_name AFTER | BEFORE | INSTEAD OF INSERT | UPDATE | DELETE AS trigger_code Kroenke, Database Processing

  21. Trigger for Complex Default Value • Trans(TransactionID, WorkID, AcquisitionPrice, AskingPrice) • ArtistWorkNet(SaleID, WorkID, NetPrice) • AskingPrice = max of • 2*AcquisitionPrice • AcquisitionPrice+AVG(Past_NetPrice), if WorkID already in table Kroenke, Database Processing

  22. Create trigger Declare variables Built-in function Kroenke, Database Processing

  23. Kroenke, Database Processing

  24. Trigger for Referential Integrity Actions – generic code Kroenke, Database Processing

  25. Class Exercise • Students(Alpha, LName, FName, GPA) • Enroll(Alpha, CourseID, Semester, Grade) • GradeValues(LetterGrade, PointValue) • Define a trigger to update the GPA every time the student gets a new grade, or a grade changes Kroenke, Database Processing

  26. 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., JavaScript, VBScript • SQL command prompt, e.g., SQL*Plus, Query Analyzer Kroenke, Database Processing

  27. 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 Kroenke, Database Processing

  28. Kroenke, Database Processing

  29. Triggers vs. Stored Procedures Kroenke, Database Processing

  30. Project 1 – Due March 3, 2006 • National College Learning Center Organization (NCLCA) www.nclca.org • Membership • Conferences organized • Have: Partial user requirements (forms) • Tasks: • Design the ER model • Transform ER model to tables • Verify tables are normalized • Write SQL to create the tables in SQL Server • Write SQL to answer typical user queries • Write-up explaining your work Kroenke, Database Processing

More Related