SQL - Trigger

SQL Server Triggers: Understanding and Alternatives

SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. 

1.Data Definition Language (DDL) events:

 CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. 

2.Data Manipulation Language (DML) events:

INSERT, UPDATE or DELETE action

The DML triggers can be used to maintain data integrity and enforce company business rules, same as table check and foreign keys constraints functionality, by performing auditing processes and other post DML actions. 

You can use the DML triggers to query other tables and perform complex T-SQL queries.

2.a.There are two types of DML triggers: AFTER or FOR trigger and INSTEAD OF trigger. 

 The AFTER trigger: fired and executed after performing the INSERT, UPDATE or DELETE action that fires it successfully. 

Also, any referential cascade actions and constraint checks should succeed before firing the trigger. 

The AFTER trigger can be defined on the table level only without the possibility define it on views. 

2.b.The INSTEAD OF trigger is used to override the statement of the action that fires the trigger with the statement provided in the trigger, rolling back that statement after raising an error when someone is trying to perform an action that breaks a specific policy, such as updating a critical financial column or writing the change into an audit table before performing the change. 

3.The triggers differ from the stored procedures in that the triggers are fired automatically when a predefined data modification occurs. 

4.If the trigger is fired, a special type of virtual tables called Inserted and Deleted tables will be used to keep the data values before and after the modification.

a.The trigger statement will work under the scope of the same transaction that fires that trigger. 
b.This means that the transaction will not be committed completely until the trigger statement is completed successfully.
c.On the other hand, the transaction will be rolled back if the trigger statement fails.

5.Trigger Alternative - Enforce Integrity

For example, rather than using the triggers to enforce the entity integrity, it should be enforced at the lowest level by using the PRIMARY KEY and UNIQUE constraints. 

The same is applied to the domain integrity that should be enforced through CHECK constraints, and the referential integrity that should be enforced through the FOREIGN KEY constraints.

You can use the DML triggers only if the features supported by a specific constraint cannot meet your application requirements.

6.Example

CREATE TRIGGER TRGR_EmployeeSalary ON EmployeeSalaryTrigger

AFTER INSERT

AS

DECLARE @EmpSal AS INT

SET @EmpSal = (SELECT TOP 1 inserted.Emp_Salary FROM inserted)

IF @EmpSal<0

BEGIN

RAISERROR  ('Cannot insert negative salary',16,10);

  ROLLBACK

END

CHECK constraint:

CREATE TABLE EmployeeSalaryConstraint

(

   ID INT IDENTITY (1,1) PRIMARY KEY,

   Emp_First_name VARCHAR (50),

   Emp_Last_name VARCHAR (50),

   Emp_Salary INT CONSTRAINT EmpSal CHECK (Emp_Salary >=0)

  )

GO

the trigger method weight is three times the CHECK constraint method weight, as shown in the execution plan 

This is due to the fact that the trigger will extend the transaction life and will rollback the query that fires the trigger after executing it when an integrity violation is found, causing a performance degradation due to the rollback process. The case is different when using the CHECK constraint, where the constraint will do its job before doing any modification in the data, requiring no rollback in the case of violation.

Also, to compare the execution time consumed by each one, let us run each one 1000 times using the T-SQL statements below:

INSERT INTO EmployeeSalaryTrigger VALUES('Ali', 'Fadi',-4)

GO 10000  

INSERT INTO EmployeeSalaryConstraint VALUES ('Ali', 'Fadi',-4)

GO 10000

7.Trigger Alternatives ... Auditing

Create a Simple SQL Server Trigger to Build an Audit Trail

Overview of SQL Server database Triggers

xml- Part 4 Modifying xml document using linq to xml

Part 1 LINQ to XML

Part 4 Modifying xml document using linq to xml

<?xml version="1.0" encoding="utf-8"standalone="yes"?>

<!--Creating an XML Tree using LINQ to XML -->

<Students>

      <Student Id="101">

            <Name>Rosy</Name>
            <Gender>Female</Gender>
            <TotalMarks>1000</TotalMarks>

      </Student>       

      <Student Id="2">

            <Name>Marina</Name>
            <Gender>Female</Gender>
            <TotalMarks>1000</TotalMarks>

      </Student> 

      <Student Id="3">

            <Name>Katrina</Name>
            <Gender>Female</Gender>
            <TotalMarks>1000</TotalMarks>

      </Student>  

</Students>     

     

--Adding new XML elements:

XDocument xmlDocument = xmlDocument.Load(@"C:\Demo\Demo\Data.xml");

xmlDocument.Element("Students").Add(
        new XElement("Student", new XAttribute("Id", 105),
        new XElement("Name","Todd"),
        new XElement("Gender", "Male"),
        new XElement("TotalMarks", "1000")
        ));

xmlDocument.save(@"C:\Demo\Demo\Data.xml");

To add the xml elements as the first element use AddFirst() method

--To add the xml elment in a specific location in the XML Document, use  AddBeforeSelf() or
-----AddAfterSelf()

xmlDocument.Element("Students")
                       .Elements("Student")
                       .Where(x -> x.Attribute("Id").value=="103").FirstOrDefault()
                       .AddBeforeSelf(
                             new XElement("Student", new XAttribute("Id",106),
                             new XElement("Name","Todd"),
                             new XElement("Gender","Male"),
                             new XElement("TotalMarks",1000)
                             ));

--Updating an xml element in the xml document

xmlDocument.Element("Students")
                       .Elements("Student")
                       .Where( x -> x.Attribute("Id").Value == "106").FirstOrDefault()
                       .SetElementValue("totalMarks", 999);

--Updating an xml comment in the xml document:

xmlDocument.Nodes().OfType<xComment>().FirstOrDefault().Value = "Comment Updated";

Sql - Sql Server Interview Questions and answers

Sql Server Interview Questions and Answers

Q1.How to find nth highest salary in SQL

Get 2nd Highest Salary using subquery.

SELECT  MAX(Salary) from Employees Where Salary <(Select MAX(Salary) from Employees)

select DISTINCT TOP 2 Salary from Employees order by Salary Desc

Salary
7000


select  salary from
(
select DISTINCT TOP 2 Salary from Employees order by Salary Desc
)RESULT
Order By Salary

Salary
7000
8000

Select TOP 1 Salary from (
select DISTINCT TOP 2 Salary from Employees order by Salary Desc
)RESULT
Order By Salary

Salary
7000

3rd highest salary:

Select TOP 1 Salary from (
select DISTINCT TOP 3 Salary from Employees order by Salary Desc
)RESULT
Order By Salary


Select Salary, DENSE_RANK( )  OVER (Order By Salary DESC) as DENSERANK
from Employees

Salary DENSERANK
8000   1
7000   2
7000   2
6000   3
5000   4
4000   3


WITH RESULT AS
(

Select Salary, DENSE_RANK( )  OVER (Order By Salary DESC) as DENSERANK
from Employees

)
Select top 1 Salary from Result where Result.DENSERANK=2

--------------------------------------------------------------------------------------------------------------------------

--To find nth highest salary using sub-query

SELECT  TOP 1 SALARY
FROM (

                  SELECT  DISTINCT  TOP N SALARY
                  FROM EMPLOYEES
                  ORDER BY SALARY DESC

                ) RESULT
ORDER BY  SALARY


--To find nth highest salary using CTE

WITH RESULT AS
(

      SELECT SALARY,
                      DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
      FROM EMPLOYEES
)
SELECT TOP 1  SALARY FROM RESULT WHERE DENSERANK = N

-------------------------------------------------------------------------------------------------------------------------


IF IT HAS DUPLICATES THE BELOW WILL NOT WORK.

WITH RESULT AS
(

      SELECT  SALARY,
                       ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
      FROM EMPLOYEES
)
SELECT SALARY FROM RESULT WHERE ROWNUMBER = N

THE ROW_NUMBER() give sequential number.

Salary  ROWNUMBER
8000    1
7000    2
7000    3
6000    4

Azure Tutorial for Beginners

Microsoft Azure Training - [1] Series Introduction (Exam 70-533)

Database-Security model-Row-ColumnLevel

------------------------------------------------------------------------------------------

                     How to Setup Row Level Security for SQL Server

------------------------------------------------------------------------------------------

How to setup such a security model?

A row-level security model for a database.

Users are connecting with their Windows user accounts.

Windows groups ( Active Directory domain(s)).

Tables: Department,Employee

Need to build a mechanism on top of these tables. 

Security Mapping Table ( DepartmentID,Windows group name)

-Tie : Security ->department->view

Preference: To create database roles 

Advantage:

To test the Model:( Windows groups yet)

- database roles ( add or remove users)

Switch domains: Production & Development ( No need to re-seeding your mapping table)

-need to associate : right Windows security groups -> right database roles

Use Ownership chaining:

        granting  SELECT permissions against the view

        Not granting any permissions against the underlying tables

-- Database roles:

-- Create database roles to go along with the Windows groups
CREATE ROLE HR_User;
GO

--Security Mapping table:

-- Create the security mapping table and populate it so we can enforce row-level security
CREATE TABLE dbo.SecurityMap (
DepartmentID INT NOT NULL,
RoleName NVARCHAR(255),
CONSTRAINT PK_SecurityMap PRIMARY KEY CLUSTERED (DepartmentID, RoleName)
);
GO 

--Finally, we need the view that uses the SecurityMap table to do the filtering:

-- Create a view which uses row-level security to filter the roles
-- Note the OR IS_MEMBER('HR_User') = 1 as well as the OR IS_MEMBER('db_owner') = 1
-- as part of the WHERE clause. If a user is a member of either of these roles,
-- then all the rows will be returned.

CREATE VIEW dbo.DepartmentEmployees
AS
SELECT Emp.FirstName, Emp.MiddleName, Emp.SurName, Dept.DepartmentName
FROM dbo.Department Dept
INNER JOIN dbo.Employee Emp
ON Dept.DepartmentID = Emp.DepartmentID
LEFT JOIN dbo.SecurityMap
ON Emp.DepartmentID = dbo.SecurityMap.DepartmentID
WHERE IS_MEMBER(dbo.SecurityMap.RoleName) = 1
OR IS_MEMBER('HR_User') = 1
OR IS_MEMBER('db_owner') = 1;
GO 

GRANT SELECT ON dbo.DepartmentEmployees TO Manager;
GRANT SELECT ON dbo.DepartmentEmployees TO HR_User;

~

Note that the "magic" is being worked by the IS_MEMBER() function. 

If the user is a member of the role which matches up to the department based on the SecurityMap table, the user sees the row containing the employee information. 

..is a member of the db_owner role, both roles of which see all the data. 

-- Query as 
CREATE USER Abc WITHOUT LOGIN;
GO 

EXEC sp_addrolemember @membername = 'Abc', @rolename = 'HR_User';
GO

EXECUTE AS USER = 'Abc';
GO

EXEC sp_addrolemember @membername = 'Abc', @rolename = 'HR_User';

GO

------------------------------------------------------------------------------------------

                     Filtering SQL Server Columns Using Column Level Permissions

------------------------------------------------------------------------------------------

~

Now, we don't want interns to have this level of permissions. We only want them to have access to specific columns. There's a way to do this. Immediately after the table name, we can specify the columns we want to grant permission to (or DENY, if we needed to do that) within a set of parentheses, like so:

GRANT SELECT ON dbo.Employee (EmployeeID, FirstName, MiddleName, SurName) TO HR_Intern;

~

Filtering Columns in SQL Server Using Views

Ownership chaining in SQL Server security feature or security risk

CREATE SCHEMA MARKETING;

GO

SELECT  
    [name] AS [schema]  
  , [schema_id] 
  , USER_NAME(principal_id) [Owner] FROM sys.schemas;

SQL Server will use the owner of the respective schemas as the "owner" for the object when ownership chaining is considered.


An object can have an explicit owner by using ALTER AUTHORIZATION on the object, as shown here (the CREATE USER statement creates a valid user to which to make the owner of the table being created):

CREATE USER TestUser WITHOUT LOGIN; GO 
CREATE TABLE Marketing.OwnedTable ( 
  TableValue INT ); GO 
ALTER AUTHORIZATION ON Marketing.OwnedTable TO TestUser; GO

SELECT  
    so.[name] AS [Object] 
  , sch.[name] AS [Schema] 
  , USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [Owner] 
  , type_desc AS [ObjectType] FROM sys.objects so 
  JOIN sys.schemas sch 
    ON so.[schema_id] = sch.[schema_id] WHERE [type] IN ('U', 'P');