Wednesday, November 16, 2016

Designing and implementing triggers in Microsoft SQL Server

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

EXECUTE AS

specifies the security context under which the trigger is executed. Enables you to control which user account the instance of SQL Server uses to validate permissions on any database objects that are referenced by the trigger. This option is required for triggers on memory-optimised tables.

NATIVE_COMPILATION
indicates that the trigger is natively compiled. This option is required for triggers on memory-optimized tables.

SCHEMABINDING
ensures that tables that are referenced by a trigger cannot be dropped or altered.
This option is required for triggers on memory-optimized tables and is not supported for triggers on traditional tables.

FOR | AFTER
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. AFTER triggers cannot be defined on views.

INSTEAD OF
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.
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. At least one option must be specified. Any combination of these options in any order is allowed in the trigger definition.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.
Triggers are classified into two main types:
1.    After Triggers (For Triggers)
2.    Instead Of Triggers

After Triggers (For Triggers)
These triggers run after an insert, update or delete on a table. They are not supported for views. 
AFTER TRIGGERS can be classified further into three types as:

1.    AFTER INSERT Trigger.
2.    AFTER UPDATE Trigger.
3.    AFTER DELETE Trigger. 
Main Table

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Jay',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('Linda',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);

Audit table for checking traces
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

After Insert Trigger:-

This trigger is fired after an INSERT on the table. Let’s create the trigger as:
CREATE TRIGGER Inserted on Employee_Test for insert
AS
DECLARE @Emp_ID INT;
DECLARE @Emp_name Varchar(100);
DECLARE @Emp_Sal Decimal (10,2);
SELECT @Emp_ID=I.Emp_ID FROM Inserted I;
SELECT @Emp_name=I.Emp_name FROM Inserted I;
SELECT @Emp_Sal=I.Emp_Sal FROM Inserted I;
INSERT INTO Employee_Test_Audit VALUES (@Emp_ID,@Emp_name,@Emp_Sal,'Inserted',getdate());
PRINT 'AFTER INSERT TRIGGER FIRED'

The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of  FOR INSERT, AFTER INSERT can be used. Both of them mean the same.

Insert one row into the table

INSERT INTO Employee_Test VALUES ('Pooja',1400);

Now check the data in table

Emp_ID            Emp_name       Emp_Sal            Audit_Action    Audit_Timestamp
13        Pooja    1400.00           Inserted            2016-11-16 12:30:35.773

After Update Trigger:-

This trigger is fired after an update on the table. Let’s create the trigger as:-

CREATE TRIGGER updated on Employee_Test for update
AS
DECLARE @Emp_ID INT;
DECLARE @Emp_name Varchar(100);
DECLARE @Emp_Sal Decimal (10,2);
SELECT @Emp_ID=I.Emp_ID FROM Inserted I;
SELECT @Emp_name=I.Emp_name FROM Inserted I;
SELECT @Emp_Sal=I.Emp_Sal FROM Inserted I;
INSERT INTO Employee_Test_Audit VALUES (@Emp_ID,@Emp_name,@Emp_Sal,'updated',getdate());
PRINT 'AFTER INSERT TRIGGER FIRED'

update Employee_Test set emp_name='pppp' where emp_id=1

now check the data in audit

Emp_ID                Emp_name         Emp_Sal               Audit_Action     Audit_Timestamp
1              pppp     1000.00 updated               2016-11-16 14:00:13.243

After Delete Trigger:-

This trigger is fired after a delete on the table. Let’s create the trigger as:

CREATE TRIGGER DELETED1 on Employee_Test for DELETE
AS
DECLARE @Emp_ID INT;
DECLARE @Emp_name Varchar(100);
DECLARE @Emp_Sal Decimal (10,2);
SELECT @Emp_ID=I.Emp_ID FROM DELETED1 I;
SELECT @Emp_name=I.Emp_name FROM DELETED1 I;
SELECT @Emp_Sal=I.Emp_Sal FROM Inserted I;
INSERT INTO Employee_Test_Audit VALUES (@Emp_ID,@Emp_name,@Emp_Sal,'DELETED',getdate());
PRINT 'AFTER INSERT TRIGGER FIRED'

CREATE TRIGGER afterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
      declare @empid int;
      declare @empname varchar(100);
      declare @empsal decimal(10,2);
      declare @audit_action varchar(100);

      select @empid=d.Emp_ID from deleted d;   
      select @empname=d.Emp_Name from deleted d;     
      select @empsal=d.Emp_Sal from deleted d; 
      set @audit_action='Deleted -- After Delete Trigger.';

      insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
      values(@empid,@empname,@empsal,@audit_action,getdate());

      PRINT 'AFTER DELETE TRIGGER fired.'
GO

To disable/enable triggers:-

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

For Specific Trigger disable

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete

INSTEAD OF:-

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.

INSTEAD OF TRIGGERS can be classified further into three types as:

  • 1.    INSTEAD OF INSERT Trigger.
  • 2.    INSTEAD OF UPDATE Trigger.
  • 3.    INSTEAD OF DELETE Trigger.

Let’s create an Instead Of Delete Trigger as:


CREATE TRIGGER InsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
      declare @emp_id int;
      declare @emp_name varchar(100);
      declare @emp_sal int;
     
      select @emp_id=d.Emp_ID from deleted d;
      select @emp_name=d.Emp_Name from deleted d;
      select @emp_sal=d.Emp_Sal from deleted d;

      BEGIN
            if(@emp_sal>1200)
            begin
                  RAISERROR('Cannot delete where salary > 1200',16,1);
                  ROLLBACK;
            end
            else
            begin
                  delete from Employee_Test where Emp_ID=@emp_id;
                  COMMIT;
                  insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
                  values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
                  PRINT 'Record Deleted -- Instead Of Delete Trigger.'
            end
      END
GO

This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed. Now, let’s try to delete a record with the Emp_Sal >1200 as:

delete from Employee_Test where Emp_ID=4

This will print an error message as defined in the RAISE ERROR statement as:

Msg 50000, Level 16, State 1, Procedure InsteadOfDelete, Line 15Cannot delete where salary > 1200Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted. 


Tuesday, November 15, 2016

User Defined Functions in SQL Server

A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

Use this statement to create a reusable routine that can be used in these ways:
·         In Transact-SQL statements such as SELECT
·         In applications calling the function
·         In the definition of another user-defined function
·         To parameterize a view or improve the functionality of an indexed view
·         To define a column in a table
·         To define a CHECK constraint on a column
·         To replace a stored procedure
·         Use an inline function as a filter predicate for a security policy

Example for scalar Function:-

The function which accepts one or more parameters and returns a Scalar/Single value.

--Transact-SQL Scalar Function Syntax 
CREATE FUNCTION dbo.test_function (@input int) --(input)
RETURNS int  -- what is this function retruning
AS 
BEGIN  
DECLARE @RET INT
SELECT @RET=
sum([ListPrice])FROM [AdventureWorks2008R2].[Production].[Product]
where productid=@input group by name
RETURN @RET --(output)
END 

To call function use below syntax

select dbo.test_function(809) As TotalListPrice

 Ex2-

CREATE FUNCTION ufn_GetFullName(@Bid int)
  RETURNS VARCHAR(30)
  AS
  BEGIN
  DECLARE @fullName VARCHAR(30)
  SELECT @fullName=
             [FirstName]+' '+[MiddleName]+' '+[LastName]
  FROM [AdventureWorks2008R2].[Person].[Person] where BusinessEntityID=@Bid
  RETURN @fullName
  END

SELECT  dbo.ufn_GetFullName(285)

SYNTAX:-
CREATE FUNCTION <FunctionName>(@Parameter <datatype>)
RETURN <DATA TYPE(SIZE)>
AS
BEGIN
DECLARE @RETURN <DATA TYPE>
FUNCTION BODY
RETURN @RETURN
END

Table-Valued Functions
The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

It will return table values

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL 
    DROP FUNCTION Sales.ufn_SalesByStore;

GO 

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) 
RETURNS TABLE 
AS 
RETURN  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' 
    FROM Production.Product AS P  
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID 
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID 
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID 
    WHERE C.StoreID = @storeid 
    GROUP BY P.ProductID, P.Name 
);

SELECT * FROM Sales.ufn_SalesByStore(602);
 

Creating a multi-statement table-valued function

The following example creates the table-valued function fn_FindReports(InEmpID) in the AdventureWorks2012 database. When supplied with a valid employee ID, the function returns a table that corresponds to all the employees that report to the employee either directly or indirectly. The function uses a recursive common table expression (CTE) to produce the hierarchical list of employees. For more information about recursive CTEs, see WITH common_table_expression (Transact-SQL).
 
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL 
    DROP FUNCTION dbo.ufn_FindReports; 

GO 

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) 
RETURNS @retFindReports TABLE  
    EmployeeID int primary key NOT NULL, 
    FirstName nvarchar(255) NOT NULL, 
    LastName nvarchar(255) NOT NULL, 
    JobTitle nvarchar(50) NOT NULL, 
    RecursionLevel int NOT NULL 

--Returns a result set that lists all the employees who report to the  
--specific employee directly or indirectly.*/ 

AS 
BEGIN 

WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns 
    AS ( 
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n 
        FROM HumanResources.Employee e  
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID 
        WHERE e.BusinessEntityID = @InEmpID 
        UNION ALL 
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor 
        FROM HumanResources.Employee e  
            INNER JOIN EMP_cte 
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode 
INNER JOIN Person.Person p  
ON p.BusinessEntityID = e.BusinessEntityID) 

-- copy the required columns to the result of the function  

   INSERT @retFindReports 
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel 
   FROM EMP_cte  
   RETURN 
END; 
GO 

-- Example invocation 
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel 

FROM dbo.ufn_FindReports(1);  
  
GO 
 

Limitations and restrictions

·         User-defined functions cannot be used to perform actions that modify the database state.
·         User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
·         User-defined functions cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.
·         Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.
·         User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
·         User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.
·         SET statements are not allowed in a user-defined function.
·         The FOR XML clause is not allowed
·         User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.
·         The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:
o    BEGIN DIALOG CONVERSATION
o    END CONVERSATION
o    GET CONVERSATION GROUP
o    MOVE CONVERSATION
o    RECEIVE
o    SEND

 

Permissions

Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. If the function specifies a user-defined type, requires EXECUTE permission on the type.