Pages

Wednesday, November 16, 2016

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 executedinstead ofthe 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

CREATETABLE Employee_Test

(

Emp_ID INTIdentity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

INSERTINTO Employee_Test VALUES ('Jay',1000);

INSERTINTO Employee_Test VALUES ('Rick',1200);

INSERTINTO Employee_Test VALUES ('Linda',1100);

INSERTINTO Employee_Test VALUES ('Stephen',1300);

INSERTINTO
Employee_Test VALUES ('Maria',1400);

Audit table for checking traces

CREATETABLE 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:

TheCREATE TRIGGERstatement
is used to create the trigger.THE ONclause
specifies the table name on which the trigger is to be attached. TheFOR INSERTspecifies
that this is anAFTER INSERTtrigger. In
place of FOR INSERT,AFTER INSERTcan be
used. Both of them mean the same.

Insert one row into the
table

INSERTINTO 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:-

Specifies that the DML trigger is executedinstead of the triggering SQL statement,
therefore, overriding the actions of the triggering statements. INSTEAD OF
cannot be specified for DDL or logon triggers.

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:

deletefrom
Employee_Test where Emp_ID=4

This will print an error
message as defined in theRAISE ERRORstatement 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

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

CREATEFUNCTION dbo.test_function(@input int)--(input)

RETURNSint-- what is this function retruning

AS

BEGIN

DECLARE @RET INT

SELECT @RET=

sum([ListPrice])FROM [AdventureWorks2008R2].[Production].[Product]

where
productid=@input groupby name

RETURN @RET --(output)

END

To call function use below syntax

select dbo.test_function(809)As TotalListPrice

Ex2-

CREATEFUNCTION ufn_GetFullName(@Bid
int)

RETURNSVARCHAR(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 columnsProductID,Name, and the aggregate of year-to-date sales asYTD Total for each product sold to the store.

Creating a multi-statement table-valued
function

The following example creates the table-valued functionfn_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, seeWITH
common_table_expression (Transact-SQL).

·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 statementscannot
be includedin
the definition of a Transact-SQL user-defined function:

oBEGIN
DIALOG CONVERSATION

oEND
CONVERSATION

oGET
CONVERSATION GROUP

oMOVE
CONVERSATION

oRECEIVE

oSEND

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.