Trigger with example

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.

Syntex:
CREATE TRIGGER [owner.]trigger_name

ON[owner.] table_name

FOR[INSERT/UPDATE/DELETE] AS

IF UPDATE(column_name)

[{AND/OR} UPDATE(COLUMN_NAME)...]

{ sql_statements }

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

For e.g
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2008R2 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

Simple Example of Cursor

Using cursor Back up of all database

DECLARE @DataBaseName VARCHAR(50)
DECLARE @Path VARCHAR(200)
DECLARE @DataBaseFileName VARCHAR(200)

SET @Path = 'E:\Backup\'

DECLARE DataBase_Cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('form')

OPEN DataBase_Cursor

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @DataBaseFileName = @Path + @DataBaseName + '.BAK'
BACKUP DATABASE @DataBaseName TO DISK = @DataBaseFileName

FETCH NEXT FROM DataBase_Cursor INTO @DataBaseName
END

CLOSE DataBase_Cursor
DEALLOCATE DataBase_Cursor

CSV Data (Comma Seperated value)

Declare @table table (CityName varchar(50))

insert into @table values ('London')
insert into @table values ('Birmingham')
insert into @table values ('Manchester')
insert into @table values ('Glasgow')

1._________________________________________________________________
declare @csv as varchar(500)
select @csv = case  when @csv is null then ''
else @csv + ',' end + CityName from @table
select @csv as csv

2.__________________________________________________________________
declare @csv as varchar(500)
SELECT @CSV = COALESCE(@csv + ', ','') + CityName
FROM @table
select @csv as csv

3.__________________________________________________________________
declare @csv as varchar(500)
set @csv = (select CityName + ',' from @table for xml path(''))
select @csv as csv

Date Formates

SELECT CONVERT(CHAR(101),getdate(),0) as "Feb  1 2012 11:11AM"
SELECT CONVERT(CHAR(101),getdate(),1) as "02/01/12"
SELECT CONVERT(CHAR(101),getdate(),2) as "12.02.01"
SELECT CONVERT(CHAR(101),getdate(),3) as "01/02/12"
SELECT CONVERT(CHAR(101),getdate(),4) as "01.02.12"
SELECT CONVERT(CHAR(101),getdate(),5) as "01-02-12"
SELECT CONVERT(CHAR(101),getdate(),6) as "01 Feb 12"
SELECT CONVERT(CHAR(101),getdate(),7) as "Feb 01,12"
SELECT CONVERT(CHAR(101),getdate(),8) as "11:13:35"
SELECT CONVERT(CHAR(101),getdate(),9) as "Feb  1 2012 11:13:53:717AM"
SELECT CONVERT(CHAR(101),getdate(),10)as "02-01-12"
SELECT CONVERT(CHAR(101),getdate(),11)as "12/02/01"
SELECT CONVERT(CHAR(101),getdate(),12)as "120201"
SELECT CONVERT(CHAR(101),getdate(),13)as "01 Feb 2012 11:15:15:107"
SELECT CONVERT(CHAR(101),getdate(),14)as "11:15:48:340"
SELECT CONVERT(CHAR(101),getdate(),20)as "2012-02-01 11:16:09"
SELECT CONVERT(CHAR(101),getdate(),21)as "2012-02-01 11:16:36.530"
SELECT CONVERT(CHAR(101),getdate(),22)as "02/01/12 11:17:11 AM"
SELECT CONVERT(CHAR(101),getdate(),23) as "2012-02-01"

SELECT CONVERT(CHAR(101),getdate(),24) as "15:10:46"
SELECT CONVERT(CHAR(101),getdate(),25) as "2012-02-01 15:11:22.310"
SELECT CONVERT(CHAR(101),getdate(),100) as "Feb  1 2012  3:11PM"
SELECT CONVERT(CHAR(101),getdate(),101) as "02/01/2012"
SELECT CONVERT(CHAR(101),getdate(),102) as "2012.02.01"
SELECT CONVERT(CHAR(101),getdate(),103) as "01/02/2012"
SELECT CONVERT(CHAR(101),getdate(),104) as "01.02.2012"
SELECT CONVERT(CHAR(101),getdate(),105) as "01-02-2012"
SELECT CONVERT(CHAR(101),getdate(),106) as "01 Feb 2012"
SELECT CONVERT(CHAR(101),getdate(),107) as "Feb 01,2012"
SELECT CONVERT(CHAR(101),getdate(),102) as "2012.02.01"
SELECT CONVERT(CHAR(101),getdate(),109) as "Feb  1 2012  3:14:15:187PM"
SELECT CONVERT(CHAR(101),getdate(),110) as "02-01-2012"
SELECT CONVERT(CHAR(101),getdate(),111) as "2012/02/01"
SELECT CONVERT(CHAR(101),getdate(),112) as "20120201"
SELECT CONVERT(CHAR(101),getdate(),113) as "01 Feb 2012 15:15:10:700"
SELECT CONVERT(CHAR(101),getdate(),114) as "15:15:36:607"
SELECT CONVERT(CHAR(101),getdate(),120) as "2012-02-01 15:15:55"
SELECT CONVERT(CHAR(101),getdate(),121) as "2012-02-01 15:16:21.857"
SELECT CONVERT(CHAR(101),getdate(),126) as "2012-02-01T15:16:38.937"
SELECT CONVERT(CHAR(101),getdate(),130) as "9 ???? ????? 1433  3:16:58:200PM"
SELECT CONVERT(CHAR(101),getdate(),131) as " 9/03/1433  3:17:13:327PM"

Count millions of records

COUNT_BIG() :

It works like a count function, difference is that count returns int value while COUNT_BIG() returns bigint.
while counting bulk of records at that time use count_big().

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * ) 

Count Duplication

create table books
(
 id int identity(1,1),
 custname varchar(25),
 bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ravi', 'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'nikhi', 'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ram', 'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'misi','evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'michel','evil')

Ans :-


SELECT bookname,COUNT(*) NoOfbooks
FROM books
GROUP BY bookname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Split Function

create FUNCTION Naimish_split
( @string varchar(max),  @Delimeter varchar(2))
RETURNS @temp TABLE
(
id int identity(1,1),
name varchar(25)
)
AS
begin
set @string = replace(@string,' ','')
declare @count int , @countDest int,@no int
select @count = 0,@countDest = LEN(@String) - LEN(REPLACE(@String, @Delimeter, ''))
while @count < @countDest
begin
set @no =  charindex(@Delimeter,@string,1)-1
insert into @temp (name) values(left(@string,@no))
set @string = right(@string,len(@string)- (@no+1))
set @count = @count + 1
end
insert into @temp(name) values(@string)
return
end

--select * from Naimish_split('Prashant,naimish,ravi,vimal,jugal,Bhadresh,mohit' , ',')

O/P: