how to create trigger in SQL Server

Discussion in 'SQL Server' started by shyam_oec, Nov 16, 2009.

  1. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    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.
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  3. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    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??
     
  4. shyam_oec

    shyam_oec New Member

    Joined:
    Nov 26, 2007
    Messages:
    89
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Software Developer, .NET Framework
    Location:
    Jamshedpur
    hi,
    can any one solve my problem?
     
  5. sql-programs

    sql-programs New Member

    Joined:
    Oct 21, 2009
    Messages:
    14
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer
    Home Page:
    http://www.sql-programmers.com
    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?
     
  6. mail.yuva

    mail.yuva New Member

    Joined:
    Jan 10, 2011
    Messages:
    44
    Likes Received:
    9
    Trophy Points:
    0
    Occupation:
    Software Developer
    Location:
    Coimbatore
  7. mountainman

    mountainman Banned

    Joined:
    Feb 6, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice