Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   how to create trigger in SQL Server (http://www.go4expert.com/forums/create-trigger-sql-server-t20105/)

shyam_oec 16Nov2009 05:36

how to create trigger in SQL Server
 
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 16Nov2009 09:24

Re: how to create trigger in SQL Server
 
Check Audit Trail in SQL Server 2000 or SQL Server 7.

I did that quite sometime back using Triggers

shyam_oec 19Nov2009 06:34

Re: how to create trigger in SQL Server
 
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 20Nov2009 06:01

Re: how to create trigger in SQL Server
 
hi,
can any one solve my problem?

sql-programs 22Dec2009 14:09

Re: how to create trigger in SQL Server
 
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 25Feb2011 17:05

Re: how to create trigger in SQL Server
 
Check this website
http://www.dotnetspark.com/kb/657-ho...e-trigger.aspx

mountainman 7Feb2012 21:59

Re: how to create trigger in SQL Server
 
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


All times are GMT +5.5. The time now is 10:27.