trigger for INSERT UPDATE DELETE in single statement

trigger for INSERT UPDATE DELETE in single statement

CREATE TRIGGER TRG ON V
FOR INSERT,UPDATE,DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'INSERTED' FROM inserted
END
IF EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'DELETED' FROM deleted
END
IF EXISTS(SELECT * FROM deleted)
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'UPDATED' FROM inserted
END
END

DYNAMIC SQL

------------------------DYNAMIC SQL-------------------

CREATE PROC usp_DYNAMIC(@empno int=null,@ename varchar(20)=null,@job varchar(10)=null,@mgr int=null)

as

BEGIN

DECLARE @SQL NVARCHAR(MAX)='SELECT * FROM EMP WHERE 1=1'

IF(@empno IS NOT NULL)

SET @SQL=@SQL+' and EMPNO='+CAST(@empno AS VARCHAR)

IF(@ename IS NOT NULL)

SET @SQL=@SQL+' OR ENAME='+CHAR(39)+@ename+CHAR(39)

IF(@job IS NOT NULL)

SET @SQL=@SQL+' OR JOB='+CHAR(39)+@job+CHAR(39)

IF(@mgr IS NOT NULL)

SET @SQL=@SQL+' OR MGR='+CAST(@MGR AS VARCHAR)

--print(@SQL)

EXEC(@SQL)

END

EXEC usp_DYNAMIC @empno=7369,@ename='ALLEN',@job='CLERK'

--backup databases

alter PROC backups 
as
BEGIN
DECLARE @DBNAME NVARCHAR(MAX)=''
DECLARE @DATE DATETIME=''
SET @DATE=GETDATE()
DECLARE BACK CURSOR
FOR SELECT NAME FROM SYS.DATABASES WHERE DATABASE_ID>4
OPEN BACK
FETCH NEXT FROM BACK INTO @DBNAME 
DECLARE @FULLNAME NVARCHAR(500)=''
DECLARE @SQL NVARCHAR(MAX)=''
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQL=('BACKUP DATABASE '+@DBNAME+' TO DISK=''D:\BACKUP\'+CAST(@DATE AS VARCHAR(100))+'.BAK''')
PRINT(@SQL)
FETCH NEXT FROM BACK INTO @DBNAME
END
CLOSE BACK
DEALLOCATE BACK
END

EXEC backups

Recursive CTE

-------------------------Recursive CTE--------------------

SELECT * INTO #TEMP FROM EMP WHERE 1=2

DECLARE @mgr int

SELECT @mgr=mgr FROM EMP WHERE empno=7369

WHILE(@mgr IS NOT NULL)

BEGIN

INSERT INTO #TEMP SELECT * FROM EMP WHERE empno=@mgr

SELECT @mgr=mgr FROM EMP WHERE empno=@mgr

END

SELECT * FROM #TEMP

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

;WITH X AS

( 

SELECT * FROM EMP WHERE EMPNO=7839

UNION ALL

SELECT E.* FROM X INNER JOIN EMP E ON X.EMPNO=E.MGR

)

SELECT * FROM X

REPORTING SERVER Expressions

--color for alternate rows
=IIF(RowNumber(NOTHING) MOD 2=0,"Plum","Khaki")

--Row Number
 =RowNumber(nothing)

--text color
=IIF(Fields!DATETIME_PRECISION.Value>1,"Red","Black")

--Count of Rows
=COUNT(Fields!TABLE_SCHEMA.Value, "DataSet1")

--SPLIT & JOIN 
=SPLIT(JOIN(Parameters!DEPTNO.Value,","),",")

--sum of salary
=Sum(Fields!SAL.Value, "DataSet1")

--sum of each dept sal
=sum(Fields!SAL.Value)

Different types of joins in SQLSERVER

--------------------------Different types of joins-----------------------

1.FULL OUTER JOIN

SELECT * FROM [table name1] T1 FULL OUTER JOIN [table name2] T2 ON 

           T1.[column name]=T2.[column name]

DECLARE @id INT

SELECT * FROM [table name] WHERE [column name]=@id  

                 OR [column name] IS NULL

MERGE statement

----------MERGE STATEMENT--------------

--if there is an IDENTITY no need to pass ID value

MERGE C AS T USING B AS S ON T.ID=S.ID AND T.NAME=S.NAME
WHEN MATCHED THEN UPDATE SET T.NAME=S.NAME
WHEN NOT MATCHED THEN INSERT VALUES(NAME) OUTPUT inserted.*,deleted.*,$ACTION;

--C target table


-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
WHEN MATCHED THEN
    UPDATE SET
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
WHEN NOT MATCHED THEN 
      INSERT (CustID, CompanyName, Phone)

      VALUES (CT.CustID, CT.CompanyName, CT.Phone);

TRIGGERs

----------------------AFTER TRIGGER----------------

--create TABLE & INSERT records in it

CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);

Insert into Employee_Demo values ('Riyaz',1400);

--create another TABLE for insert TRIGGER executions  
CREATE TABLE Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime

)

--create  TRIGGER for executions

CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS 
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'; 
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

--Now Insert record
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('suresh',1000);

--select statements

select * from Employee_Demo

select * from Employee_Demo_Audit