Ch 5' Introducing More Database Objects

1
Ch 5. Introducing More Database Objects
2
Database Objects

• Table (ch2)
• View (ch3)
• Stored Procedure
• Trigger
• Function
• User-defined types

3
Stored Procedures

• A collection of code
• stored as an object in the database
• Accepts parameters
• Allows all T-SQL statements
• Can be called with EXECUTE statement

4
Types of Stored Procedures

• System Stored Procedures
• T-SQL Stored Procedures
• CLR Procedures
• Extended stored procedures

5
System Stored Procedure

• Pre-defined stored procedures to perform
  maintenance and management activities

6
System Stored Procedures
7
System Stored Procedure

• sp_help, xp_logininfo, sp_monitor, sp_who etc

8
T-SQL Stored Procedures

• Benefit
• Code Reusability
• reduce network traffic centrally stored on the
  server
• Permission-based execution only user with valid
  permission can execute it
• Security Prevent SQL Injection attacks

9
T-SQL Stored Procedure

• Create a table using following code
• CREATE TABLE Employees(
• EmployeeID int IDENTITY(1,1) Primary key NOT
  NULL,
• Employeename nvarchar(200) NULL,
• EmployeeSSN int NULL,
• msg nchar(10) NULL
• )

10
T-SQL Stored Procedure

• TO create a stored procedure to insert
• CREATE PROCEDURE InsertEmployee
• (
• _at_Name nvarchar(200),
• _at_SSN int
• )
• AS
• INSERT INTO Employees
• (EmployeeName, EmployeeSSN)
• VALUES
• (_at_Name, _at_SSN)

11
T-SQL Stored Procedure

• To test our stored procedure
• InsertEmployee Tester , 123456789
• Go
• InsertEmployee Tester2 , 987654321
• Go
• InsertEmployee Tester3 , 111111111
• Go

12
T-SQL Stored Procedure

• TO create a stored procedure to Update a row
• CREATE PROCEDURE UpdateEmployee
• (
• _at_ID int,
• _at_Name nvarchar(200),
• _at_SSN int
• )
• AS
• UPDATE
• Employees
• SET
• EmployeeName_at_Name,
• EmployeeSSN_at_SSN
• WHERE
• EmployeeID _at_ID

13
T-SQL Stored Procedure

• To test our stored procedure
• UpdateEmployee 0 , Update Tester ,123456789

14
T-SQL Stored Procedure

• TO create a stored procedure to Delete a row
• CREATE PROCEDURE DeleteEmployee
• (
• _at_ID int
• )
• AS
• DELETE FROM
• Employees
• WHERE
• EmployeeID _at_ID

15
T-SQL Stored Procedure

• To test our stored procedure
• DeleteEmployee 0

16
CLR Stored Procedure

• Access to objects/methods outside of MS SQL
  Server 2005
• Uses .Net CLR (Common Language runtime)
• For complex calculations or methods that require
  access to objects outside of SQL Server
• Exercise 5.1

17
Triggers

• Group of code that will automatically execute
  when a certain event occur
• DML trigger (Data Manipulation Language) -
  Insert, Update, Delete table opereations
• DDL Trigger (Data Defination Language)
  database/Server events

18
DML Insert Trigger

• CREATE TRIGGER trg_Insert
• ON dbo.Employees
• FOR INSERT
• AS
• DECLARE _at_ID int
• SELECT _at_ID EmployeeID FROM INSERTED
• UPDATE
• Employees
• SET
• msg 'Inserted'
• WHERE
• EmployeeID _at_ID

19
DML Update Trigger

• CREATE TRIGGER trg_Update
• ON dbo.Employees
• FOR UPDATE
• AS
• DECLARE _at_ID int
• SELECT _at_ID EmployeeID FROM DELETED
• IF UPDATE(EmployeeName)
• UPDATE
• Employees
• SET
• msg 'Updated'
• WHERE
• EmployeeID _at_ID

20
DML Delete Trigger

• CREATE TRIGGER trg_Delete
• ON dbo.Employees
• FOR DELETE
• AS
• DECLARE _at_ID int
• SELECT _at_ID EmployeeID FROM DELETED
• ROLLBACK
• UPDATE
• Employees
• SET
• msg 'Deleted'
• WHERE
• EmployeeID _at_ID

21
DML Instead of Trigger

• Completely skip the insert/update/delete event,
  then execute the trigger
• Still populates INSERTED, DELETED internal table

22
DML Instead of Trigger

• CREATE TRIGGER trg_InsteadUpdate
• ON dbo.Employees
• INSTEAD OF UPDATE
• AS
• BEGIN
• IF UPDATE(EmployeeName)
• DECLARE _at_ID int
• SELECT _at_ID EmployeeID FROM DELETED
• UPDATE
• Employees
• SET
• msg 'I_Update'
• WHERE
• EmployeeID _at_ID
• END

23
DML Instead of Trigger

• CREATE TRIGGER trg_InsteadDelete
• ON dbo.Employees
• INSTEAD OF DELETE
• AS
• BEGIN
• DECLARE _at_ID int
• SELECT _at_ID EmployeeID FROM DELETED
• UPDATE
• Employees
• SET
• msg 'I_Deleted'
• WHERE
• EmployeeID _at_ID
• END

24
DDL Trigger

• A group of code to automatically fire on any DDL
  event (create table, drop table )
• New in SQL 2005
• Page 156-157

25
DDL Trigger

• CREATE TRIGGER trg_block_ddl ON DATABASE
• FOR DDL_DATABASE_LEVEL_EVENTS
• AS
• RAISERROR('database locked for ddl events',16,1)
• ROLLBACK TRANSACTION

26
Trigger Nesting

• A trigger event that was fired by another trigger
• SQL Server supports up to 32 levels, after which
  the trigger will be canceled
• Disable Trigger Nesting
• SP_CONFIGURE nested_triggers,0
• RECONFIGURE

27
Trigger Recursion

• Direct Recursion Trigger perform a statement
  that will cause itself to be fired
• InDirect Recursion Trigger1 perform a statement
  that will fire Trigge2 which will fire Trigger1,
  repeat

28
Trigger Recursion

• CREATE TRIGGER trg_RecursiveInsert
• ON dbo.Employees
• FOR INSERT
• AS
• DECLARE _at_Name nvarchar(200)
• DECLARE _at_SSN int
• SELECT
• _at_Name EmployeeName,
• _at_SSN EmployeeSSN
• FROM
• Employees
• EXECUTE InsertEmployee _at_Name, _at_SSN

29
Disable Trigger Recursion

• Disable (Direct) Recursive Trigger
• ALTER DATABASE databasename
• SET RECURSIVE_TRIGGER OFF
• Disable (inDirect) Recursive Trigger
• SP_CONFIGURE nested_triggers,0
• RECONFIGURE

30
Functions

• Similar to a stored procedure except it can be
  called within a SELECT statement
• Type of Function
• Scalar Function
• Table-valued function
• Built-in function
• CLR function

31
Functions

• Create this table
• CREATE TABLE dbo.EmployeeInfo
• (
• EmployeeInfoID int IDENTITY(1,1) PRIMARY KEY
  NOT NULL,
• EmployeeID int NOT NULL,
• DOB datetime NULL,
• )

32
Scalar Functions

• CREATE FUNCTION dbo.Age
• (
• _at_DOB datetime
• )
• RETURNS int
• AS
• BEGIN
• DECLARE _at_Age int
• SELECT _at_Age Datediff(yyyy,_at_DOB,GetDate())
• RETURN _at_Age
• END
• GO

33
Scalar Functions

• To test our function
• SELECT
• ,
• dbo.Age(EmployeeInfo.DOB)
• FROM
• Employee INNER JOIN EmployeeInfo
• ON
• Employee.EmployeeID EmployeeInfo.EmployeeID

34
Table-valued Functions

• CREATE FUNCTION dbo.getTable ()
• RETURNS TABLE
• AS
• (
• SELECT
• Employee.EmployeeName ,
• dbo.Age(EmployeeInfo.DOB) AS AGE
• FROM
• Employee INNER JOIN EmployeeInfo
• ON
• Employee.EmployeeID EmployeeInfo.EmployeeID
• )

35
Table-valued Functions

• Can be used to return a whole table
• To test our table-valued function
• select from getTable()

36
Built-in Functions

• Much like built-in stored procedures

37
Function - Determinism

• Deterministic function - always return the same
  value
• Non-Deterministic Function may return different
  value each time

38
Function - Determinism

• A Non-Deterministic function
• CREATE FUNCTION getTime
• (
• _at_ExtraDay int
• )
• RETURNS datetime
• AS
• BEGIN
• RETURN DateAdd(day,_at_ExtraDay, GetDate())
• END
• GO

39
Function - Determinism

• A Deterministic function
• CREATE FUNCTION getSquare
• _at_InputNumber int
• )
• RETURNS int
• AS
• BEGIN
• RETURN (_at_inputNumber_at_InputNumber)
• END
• GO

40
User-Defined Types

• Create an alias to an existing data type
• Only have ability to set length and NOT NULL
  option
• Created using SP_addtype

41
User-Defined Types

• Open a new database query in SYBEX
• EXEC sp_addtype ssn, VARCHAR(11), NOT NULL
• Create a table with user defined type
• CREATE TABLE Employees
• (EmployeeID int identity (1,1),
• Employeename nvarchar(200),
• DepartmentID int,
• EmployeeSSN ssn)