locked
Insert Trigger RRS feed

  • Question

  • I am trying create a trigger to insert minor setting changes from multiple production databases into a master database, which inturn is used as a model for any newly created production databases.  My code is as follows:

     

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [dbo].[INSERT_INTEL_PROTO] ON [dbo].[DIALOGS]

    AFTER INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    UPDATE PRJ_PROT.dbo.DIALOGS

    SET    WIDTH = A.WIDTH,

    HEIGHT = A.HEIGHT,

    CAPTION = A.CAPTION,

    GRIDX = A.GRIDX,

    GRIDY = A.GRIDY

    FROM PRJ_1.dbo.DIALOGS A,

    PRJ_PROT.dbo.DIALOGS B

    WHERE A.DIALOGNAME = B.DIALOGNAME

    END

     

    I am not getting any errors, but the newly inserted rows on the production DB are not coping into the master, any thoughts?  Also if anyone has an idea on how to make the current production DB a variable let me know, as you can tell I have the production DB hardcoded in and this would need to be changed everytime we created a new one.

    Thursday, October 16, 2008 5:36 PM

Answers

  • INSERTED is a table that hold the value of current rows that are inserted in one perticular batch here is an example

     

    Code Snippet

    CREATE TABLE LogSummary (LogSum_Category varchar(30), LogSum_Count int)

    go

     

     

    Code Snippet

    CREATE TRIGGER tr_InetLog_INSERT

    ON LogSummary

    FOR INSERT

    AS

    SELECT * FROM INSERTED

     

     

    Code Snippet

    INSERT LogSummary VALUES ('About Us',0)

    LogSum_Category LogSum_Count

    ------------------------------ ------------

    About Us 0

     

     

    So whatever get inserted in the current Batch statement comes in INSRTED. similarly there is DELETED too.

     

    Since this narrows down the JOIN on a smaller subset using this will make you DML in trigger faster.

     

    Mind it trigger fires for each row insert /update/delete . so keep it small and atomic to get best DML performance

    Monday, October 20, 2008 5:28 PM

All replies

  •  

     

    Your second question first.

     

    How to pass database id to variable

     

    Code Snippet

    declare @db_id int,@db_name varchar(20)

    SELECT @db_id=DB_ID() , @db_name=db_name(db_id())

    select @db_id as DatabaseID ,@db_name as databaseName

     

     

    Now if you are asking that your update is not happening and what is the reason.

     

    Only reason I can think of is

     

    WHERE A.DIALOGNAME = B.DIALOGNAME  is not getting validate. ie there are no common rows.

     

    Also I cannot fail to notice that yuor trigger actually whole table from DB to another DB each time it is fired and it will be fired after each update. This is not correct design of trigger.

     

    Use INSERTED tables to take only those rows that has been inserted in this transaction and use this table to JOIN to destination table and update .( they way you are doing , even if works will have a perf hit)

     

    Hope that helps

    Thursday, October 16, 2008 5:52 PM
  • You're asking why "newly inserted rows on the production db aren't copying into the master", but your trigger isn't INSERTing rows into the master, it's updating them.  If you're inserting a brand new row into dbo.Dialogs (as your trigger suggests), then you'll need to INSERT into the master database...not try to update an existing record.  As Ashwani said, there probably isn't a match in your master database yet!
    Thursday, October 16, 2008 6:20 PM
  • Ashwani I am still fairly new at this, so I apologize in advance if you questions are rudimentary.  I understand that the WHERE statement is not getting validated, that makes sense.  I have also changed my UPDATE statement to an INSERT statement but I do not understand your suggestion to use INSERTED tables, could you explain this a bit more with code so that I can see it more clearly?

     

    Monday, October 20, 2008 4:56 PM
  • INSERTED is a table that hold the value of current rows that are inserted in one perticular batch here is an example

     

    Code Snippet

    CREATE TABLE LogSummary (LogSum_Category varchar(30), LogSum_Count int)

    go

     

     

    Code Snippet

    CREATE TRIGGER tr_InetLog_INSERT

    ON LogSummary

    FOR INSERT

    AS

    SELECT * FROM INSERTED

     

     

    Code Snippet

    INSERT LogSummary VALUES ('About Us',0)

    LogSum_Category LogSum_Count

    ------------------------------ ------------

    About Us 0

     

     

    So whatever get inserted in the current Batch statement comes in INSRTED. similarly there is DELETED too.

     

    Since this narrows down the JOIN on a smaller subset using this will make you DML in trigger faster.

     

    Mind it trigger fires for each row insert /update/delete . so keep it small and atomic to get best DML performance

    Monday, October 20, 2008 5:28 PM
  • Thanks Ashwani, that worked out great!

     

    Monday, October 20, 2008 8:15 PM