Professional Web Applications Themes

Using Instead of insert - Microsoft SQL / MS SQL Server

/*simple one this. you've forgotten to put in the line INSERT INTO myPrimary SELECT * FROM inserted so that your code should read... */ CREATE TABLE myPrimary( c1 int primary key, c2 varchar(100) default 'Primary') GO CREATE TABLE mySecondary( c1 int primary key, c2 varchar(100) default 'Secondary') GO CREATE TRIGGER myTrig on myPrimary instead of INSERT AS INSERT INTO myPrimary SELECT * FROM inserted INSERT INTO mySecondary SELECT * FROM inserted GO INSERT INTO myPrimary values(1,'New value') /*as you probably know the instead of trigger captures the action and fires *but* doesnt cause the action to occur -hence no values ...

  1. #1

    Default Re: Using Instead of insert

    /*simple one this. you've forgotten to put in the line
    INSERT INTO myPrimary SELECT * FROM inserted

    so that your code should read... */
    CREATE TABLE myPrimary(
    c1 int primary key,
    c2 varchar(100) default 'Primary')
    GO
    CREATE TABLE mySecondary(
    c1 int primary key,
    c2 varchar(100) default 'Secondary')
    GO
    CREATE TRIGGER myTrig on myPrimary instead of INSERT AS
    INSERT INTO myPrimary SELECT * FROM inserted
    INSERT INTO mySecondary SELECT * FROM inserted
    GO
    INSERT INTO myPrimary values(1,'New value')
    /*as you probably know the instead of trigger captures the action and fires
    *but* doesnt cause the action to occur -hence no values in the primary
    table*/

    --
    BR,

    Mark Broadbent mcdba,mcse+i
    _________________________
    "hngo01" <com> wrote in message
    news:0ee701c35765$8c1ed490$gbl... 


    Mark Guest

  2. #2

    Default Re: Using Instead of insert

    Since this an INSTEAD OF trigger, no actions are automatically performed
    on the underlying table. You'll need to insert into the myPrimary table
    manually:

    CREATE TRIGGER myTrig on myPrimary INSTEAD OF INSERT AS
    INSERT INTO mySecondary SELECT * FROM inserted
    INSERT INTO myPrimary SELECT * FROM inserted
    GO

    Note that you can achieve the same result more efficiently with a
    regular AFTER trigger:

    CREATE TRIGGER myTrig on myPrimary FOR INSERT AS
    INSERT INTO mySecondary SELECT * FROM inserted
    GO

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "hngo01" <com> wrote in message
    news:0ee701c35765$8c1ed490$gbl... 


    Dan Guest

  3. #3

    Default Re: Using Instead of insert

    Your trigger must to ALL of the work - including the actual insert into myPrimiary. Try:

    CREATE TRIGGER myTrig on myPrimary instead of INSERT AS
    INSERT INTO myPrimary SELECT * FROM inserted
    INSERT INTO mySecondary SELECT * FROM inserted
    GO


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "hngo01" <com> wrote in message news:0ee701c35765$8c1ed490$gbl...
    Hi all- please look at the script below. When I run this
    script, I thought it would produce the same result as
    regular "Trigger Insert". BUT it is not. I see no value
    inserted into my myPrimary table. I want it to insert into
    my myPrimary too. How do I do that with "INSTEAD OF
    INSERTED? Thanks

    CREATE TABLE myPrimary(
    c1 int primary key,
    c2 varchar(100) default 'Primary')
    GO
    CREATE TABLE mySecondary(
    c1 int primary key,
    c2 varchar(100) default 'Secondary')
    GO
    CREATE TRIGGER myTrig on myPrimary instead of INSERT AS
    INSERT INTO mySecondary SELECT * FROM inserted
    GO
    INSERT INTO myPrimary values(1,'New value')


    Tom Guest

  4. #4

    Default Re: Using Instead of insert

    Thanks but if my application calling a Stored Procedure
    below, then what is the order of the execution? Which one
    got fired first?

    CREATE PROCEDURE dbo.InsertValue
    V1 int,
    V2 VarChar(50)
    AS
    insert into myPrimary(c1,c2)
    values(V1,V2)
    GO

    Also - Two insert statements got executed right? I'm
    confused. (One is in Stored Procedure and one is in the
    Instead of INSERT Trigger? Is that true? Thanks

     
    automatically performed 
    myPrimary table 
    efficiently with a [/ref]
    this [/ref]
    value [/ref]
    into 
    >
    >
    >.
    >[/ref]
    hngo01 Guest

  5. #5

    Default Re: Using Instead of insert

    The INSTEAD OF trigger intercepts all of your INSERT statements, whether they come from stored procs or ad hoc SQL. Thus, the INSERT from inside your proc only fires the trigger. The INSERT inside the trigger does the actual insert.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "hngo01" <com> wrote in message news:023701c35854$7b969870$gbl...
    Thanks but if my application calling a Stored Procedure
    below, then what is the order of the execution? Which one
    got fired first?

    CREATE PROCEDURE dbo.InsertValue
    V1 int,
    V2 VarChar(50)
    AS
    insert into myPrimary(c1,c2)
    values(V1,V2)
    GO

    Also - Two insert statements got executed right? I'm
    confused. (One is in Stored Procedure and one is in the
    Instead of INSERT Trigger? Is that true? Thanks

     
    automatically performed 
    myPrimary table 
    efficiently with a [/ref]
    this [/ref]
    value [/ref]
    into 
    >
    >
    >.
    >[/ref]
    Tom Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Multiple Insert or Looping Insert
    By payado in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 25th, 09:33 PM
  4. Replies: 0
    Last Post: July 23rd, 11:30 PM
  5. Replies: 3
    Last Post: September 30th, 09:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139