how to create trigger in SQL Server

shyam_oec's Avatar, Join Date: Nov 2007
Contributor
Dear Sir,
I am working with SQL Server 2005,provided by default by Visual Studio 2008.I am comfortable with Oracle regarding creating triggers etc.But i don't know how and where to create Trigger in SQL Server.
plz solve it.
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Check Audit Trail in SQL Server 2000 or SQL Server 7.

I did that quite sometime back using Triggers
shyam_oec's Avatar, Join Date: Nov 2007
Contributor
hi,
i read the article,trigger too is created but at run time error occurs.
It is returning error something like :

Subquery returnes more than 1 value.This is not permitted when the subquerry follows=,!=,<,..... or when the subquery is user as an expression.


My Trigger Body is:

ALTER TRIGGER Trigger1
ON dbo.sal_t
FOR INSERT
AS
begin
declare @id1 int
set @id1=(select id from sal_t)
insert into emp_t values(@id1,'ram')
end


TABLE SCHEMA ARE:
Table- sal_t:
id int,
sal int

Table- emp_t
id int,
name varchar(20)

so,where i am wrong??
shyam_oec's Avatar, Join Date: Nov 2007
Contributor
hi,
can any one solve my problem?
sql-programs's Avatar, Join Date: Oct 2009
Go4Expert Member
In your trigger "Trigger1" the script line

set @id1=(select id from sal_t)

result in more than one row when you are inserting the second row to the "sal_t" table
So it result in the error "Subquery returnes more than 1 value.This is not permitted when the subquerry follows=,!=,<,..... or when the subquery is user as an expression."

Alter the trigger as follows,

alter TRIGGER Trigger1
ON dbo.sal_t
FOR INSERT
AS
begin
declare @id1 int
set @id1=(select top 1 id from sal_t)
insert into emp_t values(@id1,'ram')
end


It solves your problem. But the Trigger is not meaningfull. Can you please tell me the purpose of the trigger so that I can give you the response. Do you want to insert the last inserted id to emp_t table with the name as ram?
mail.yuva's Avatar, Join Date: Jan 2011
Go4Expert Member
Check this website
http://www.dotnetspark.com/kb/657-ho...e-trigger.aspx
mountainman's Avatar
Banned
Hope this will help

CREATE TRIGGER [ schema_name . ]trigger_name
ON table_name
AFTER
INSERT
AS
@Username = select id from inserted
insert into AUTHEN(us,pw) values(@username,'password')
go