how to create triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to create triggers

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2011-12-12 : 07:56:30

how to create triggers

please give simple example for insert triggers

1:- when we insert record into table then triggers automatically fire
for insert unique number

pls provide simple examples with table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:10:02

you can use CREATE TRIGGER statement for creating triggers
Trigger can be for any of DML actions like insert,delete or update or can even be for DDL action or LOGON events

more details below
http://msdn.microsoft.com/en-us/library/ms189799.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunnyleoneo
Starting Member

19 Posts

Posted - 2011-12-12 : 12:27:44

Hi,

Go through this nice article http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

paritosh
Starting Member

42 Posts

Posted - 2011-12-13 : 00:14:39

pls do not give link .
pls provide simple example .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 01:10:57

quote:
Originally posted by paritosh

pls do not give link .
pls provide simple example .

First go through samples in link and try it yourself. In case you face any issue post and we will help.
Dont expect somebody to spoonfeed you with code you want. You wont benefit anything from that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunnyleoneo
Starting Member

19 Posts

Posted - 2011-12-13 : 02:15:00

Please post your table structure and trigger that you have made after looking at the article. If you are not able to achieve your desired thing then we will definitely help you

paritosh
Starting Member

42 Posts

Posted - 2011-12-13 : 02:37:35

quote:
Originally posted by sunnyleoneo

Please post your table structure and trigger that you have made after looking at the article. If you are not able to achieve your desired thing then we will definitely help you

suppose i have a table xyz(id numeric(10),name varchar(10))

if i want to create trigger .which is insert every time id column values automatically when name column is manually inserted.

example:- insert into xyz (name)values('aa')
then trigger fire and insert id value.

if you understand. so pls give answer with example.

paritosh
Starting Member

42 Posts

Posted - 2011-12-13 : 03:39:06

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:20:05

quote:
Originally posted by paritosh

suppose i have a table xyz(id numeric(10),name varchar(10))

if i want to create trigger .which is insert every time id column values automatically when name column is manually inserted.

example:- insert into xyz (name)values('aa')
then trigger fire and insert id value.

if you understand. so pls give answer with example.

why should you use trigger for this?
you just need to make id column of identity type and then it will generate the value automatically on each insertion. hence no need of trigger.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paritosh
Starting Member

42 Posts

Posted - 2011-12-13 : 04:22:15

i know identity works very fine but i want to know this by trigger.
so pls give a trigger example .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:34:27

quote:
Originally posted by paritosh

i know identity works very fine but i want to know this by trigger.
so pls give a trigger example .

why do you want to complicate things?

Anyways here you go


CREATE TRIGGER GetID
ON Yourtable
FOR INSERT
AS
BEGIN
SELECT IDENTITY(int,1,1) AS IDCol,*
INTO #Temp
FROM INSERTED

UPDATE t
SET t.ID=COALESCE((SELECT MAX(ID) FROM Table),0) + tmp.IDCol
FROM Table t
INNER JOIN #Temp tmp
ON tmp.PK = t.PK

DROP TABLE #Temp
END

pk is your primary key

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paritosh
Starting Member

42 Posts

Posted - 2011-12-13 : 07:08:31

quote:
Originally posted by visakh16

quote:
Originally posted by paritosh

i know identity works very fine but i want to know this by trigger.
so pls give a trigger example .

why do you want to complicate things?
Anyways here you go

CREATE TRIGGER GetID
ON Yourtable
FOR INSERT
AS
BEGIN
SELECT IDENTITY(int,1,1) AS IDCol,*
INTO #Temp
FROM INSERTED

UPDATE t
SET t.ID=COALESCE((SELECT MAX(ID) FROM Table),0) + tmp.IDCol
FROM Table t
INNER JOIN #Temp tmp
ON tmp.PK = t.PK

DROP TABLE #Temp
END

pk is your primary key

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thanks for this .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 07:28:24

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources