Search

SQL Server - Audit Trigger



What Audit trigger here will do:

Audit trigger – to audit DML operations on all tables in a database.
Audit trigger is created by using stored procedure “GenerateAudittrail” & function “Upd_trg_col_populate”.

What  stored procedure “GenerateAudittrail” here will do :
 
Create exact copy of all tables in a database with extra audit columns and name suffixed as ‘_audit’.
Create Insert/Update/Delete trigger for all original  tables .
Triggers are created in such a way that whenever DML operations take place on all original  tables ,audit tables will be populated accordingly.

What function “Upd_trg_col_populate” here will do:

In case of update operation,only updated column value of that table along with audit columns will get populated in audit tables
“Upd_trg_col_populate” function is called inside GenerateAudittrail” procedure.


Step 1: --create test data

Sql Server - INSTEAD OF TRIGGER

INSTEAD OF TRIGGER -

 It is used to perform your desired operations instead of INSERT/UPDATE/DELETE on table or view by making use of magic tables which gets populated as a result of DML operations.

EXAMPLE 1:
CREATE DATABASE TEST_INSTEAD_OF_TRG
GO

USE TEST_INSTEAD_OF_TRG
GO

inserting into view which has two or more tables joined using INSTEAD OF TRIGGER

-- Create two tables
-- student table

CREATE TABLE STUDENT(

ID INT,
Name VARCHAR(20))
GO

 
INSERT INTO STUDENT SELECT 1,'sathya'
GO

--department table

CREATE TABLE DEPARTMENT(

ID INT,
DEPT_Name VARCHAR(20))
GO

 
INSERT INTO DEPARTMENT SELECT 1,'ECE'
GO

--create view joining student & department table

CREATE VIEW VW_INSTEAD_OF_TRG
AS
SELECT a.ID,a.Name,b.DEPT_Name FROM STUDENT a 

JOIN DEPARTMENT b ON a.ID = b.ID

-- try to insert into view
INSERT INTO VW_INSTEAD_OF_TRG SELECT 2,'deepak','CS'
GO

you will get the below error message,because you cannot insert into view with two or more tables joined


Msg 4405, Level 16, State 1, Line 1
View or function 'VW_INSTEAD_OF_TRG' is not updatable because the modification affects multiple base tables.


--solution,create instead of trigger,as shown below

CREATE TRIGGER INSTEAD_OF_TRG ON VW_INSTEAD_OF_TRG
INSTEAD OF INSERT
AS
INSERT INTO STUDENT (ID,Name) SELECT ID,Name FROM INSERTED

INSERT INTO DEPARTMENT (ID,DEPT_Name) SELECT ID,DEPT_Name FROM INSERTED

--Again try to insert
INSERT INTO VW_INSTEAD_OF_TRG SELECT 2,'deepak','CS'
GO


--you will notice records inserted into view with joined tables
 
SELECT * FROM VW_INSTEAD_OF_TRG
SELECT * FROM STUDENT
SELECT * FROM DEPARTMENT

EXAMPLE 2:to avoid default values in table getting updated/inserted 


--create table with default value option  for few columns

CREATE TABLE Login_Audit (

ID INT,
NAME VARCHAR(20),
Last_Login_User VARCHAR(20) DEFAULT SYSTEM_USER,
Last_Login_Date DATE DEFAULT GETDATE())
GO


--try to populate the table (including default value column)  with values
 
INSERT INTO Login_Audit (ID,NAME,Last_Login_User,Last_Login_Date) SELECT 1,'Sathya','Hacker',GETDATE()-365
GO

--If you run the below query,you will notice default values getting updated
SELECT * FROM Login_Audit

--Solution,create instead of trigger,as shown below

CREATE TRIGGER INSTEAD_OF_TRGG ON Login_Audit
INSTEAD OF INSERT
AS
INSERT INTO Login_Audit (ID,NAME) SELECT ID,NAME FROM inserted

--try again to override the default values

INSERT INTO Login_Audit (ID,NAME,Last_Login_User,Last_Login_Date) SELECT 1,'Sathya','Hacker',GETDATE()-365
GO

--you will notice ,instead of trigger has prevented default values getting updated
SELECT * FROM Login_Audit



DROP DATABASE TEST_INSTEAD_OF_TRG

Sql Server - Object dependencies


Dependencies by creating Primary & Foreign keys:

 

CREATE DATABASE TEST_DEPENDENCY_DB1
GO 
USE TEST_DEPENDENCY_DB1
GO
CREATE TABLE TEST_DEPENDENCY_PRIMARY(EMPID INT PRIMARY KEY,NAME VARCHAR(10))
INSERT INTO TEST_DEPENDENCY_PRIMARY SELECT 1 ,'Sathya'
INSERT INTO TEST_DEPENDENCY_PRIMARY SELECT 2 ,'Deepak'
CREATE TABLE TEST_DEPENDENCY_SECONDARY(EMPID INT FOREIGN KEY REFERENCES TEST_DEPENDENCY_PRIMARY (EMPID),[ROLE] VARCHAR(10))
INSERT INTO TEST_DEPENDENCY_SECONDARY SELECT 1 ,'Developer'
INSERT INTO TEST_DEPENDENCY_SECONDARY SELECT 2 ,'DBA'
Under Object Explorer, right click Table - > View Dependencies
After creating foreign key constraint, you can see dependencies created for tables  TEST_DEPENDENCY_PRIMARY & TEST_DEPENDENCY_SECONDARY

 

Sql Server - DDL triggers & EVENTDATA() function


DDL Trigger:
DDL triggers are fired whenever Data Definition Language (DDL) events  like Create, Alter, Drop, or Data Control Language (DCL) events   like Grant, Deny or Revoke occurs.

 DDL Triggers is useful for the following purpose:
Can be used by DBA ‘s:
• to Issue an alert when a DDL event occurs in Database or Server.
to log information about database-level DDL events and Server-level DDL events.
• to Secure the database or server from malpractice.

DDL triggers scope is of two types:
1.)Server level
2.)Database level

Refer below link,for more information about different  Scope of DDL triggers.

By executing below query,we can get hierarchy list of DDL events & DDL grouped events.
WITH DirectReports(name, parent_type, type, level, sort) AS
(
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
    FROM sys.trigger_event_types
    WHERE parent_type IS NULL
    UNION ALL
SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),
e.parent_type, e.type, level + 1,
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)
    FROM sys.trigger_event_types AS e
        INNER JOIN DirectReports AS d
        ON e.parent_type = d.type
)
SELECT parent_type, type, name
FROM DirectReports
ORDER BY sort;


Sql server gives flexibility to handle DDL events in DDL triggers,you can see that in the highlighted section of below image.

For example in below image ,

Sql Server - All about Statistics




STATISTICS

Statistics holds information about distribution of data in tables & indexes.
Statistics helps the optimiser in creating efficient plan.

EXAMPLES:

 SET  “Auto Create Statistics ” & “Auto Update Statistics” in database properties   =   ‘FALSE’

CREATE TABLE TEST (Col1  INT ,Col2 VARCHAR(20),Col3 VARCHAR(20))

;WITH CTE (Col1,Col2,Col3)
AS(
SELECT Col1 = 1,Col2 = 'unique column' + CAST(1 AS VARCHAR(20)),col3 = 'nonunique column'
UNION ALL
SELECT Col1 = Col1 + 1,Col2 = 'unique column' + CAST(Col1 + 1 AS VARCHAR(20)),col3 = 'nonunique column'
FROM CTE
WHERE Col1 < 30000
)
INSERT INTO TEST
SELECT * FROM CTE OPTION (MAXRECURSION 30000)


sp_helpstats  - Returns statistics information about columns and indexes on the specified table.

sp_helpstats 'TEST', 'ALL'
GO

You will notice below message:
This object does not have any statistics or indexes.


Try the below query:
SELECT * FROM TEST WHERE Col1 = 1500


Sql Server - Various observations on indexes


First lets us create table & insert sample data

CREATE TABLE TEST (Col1  INT ,Col2 VARCHAR(20),Col3 NVARCHAR(20))

WITH CTE (Col1,Col2,Col3)
AS(
SELECT Col1 = 1,Col2 = 'varchar column' + CAST(1 AS VARCHAR(20)),col3 = 'nvarchar column' + CAST(1 AS NVARCHAR(20))
UNION ALL
SELECT Col1 = Col1 + 1,Col2 = 'varchar column' + CAST(Col1 + 1 AS VARCHAR(20)),col3 = 'nvarchar column' + CAST(Col1 + 1 AS NVARCHAR(20))
FROM CTE
WHERE Col1 < 30000
)

INSERT INTO TEST
SELECT * FROM CTE OPTION (MAXRECURSION 30000)
SELECT * FROM TEST

Note: Press ctrl + M/ctrl + L ,to see the execution plans for all below examples.

---------------------------------------------------------------
--Example 1

SqlServer - Copy-Only Backups




Database – Restoration process: 


While restoring database , the differential database backups will have a reference to the full database backup , i.e through LSN (Log sequence number) .

If a full database backup is taken , any subsequent differential database backups
use this latest full database backup as their base .  


Hence ,database is restored with the help of sequence of LSN .
                 
LSN 's are stored with the backup file is to verify that the  backup files are being restored in the proper sequence .

SSIS -Solution for mixed data types problem while importing from excel

Goto the properties of the Excel Connection manager and change the ConnectionString property:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Excelname.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text

IMEX = 0 is Export mode
IMEX = 1 is Import mode
IMEX = 2 is Linked mode