Insert Trigger

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.

Answer 1

 

 

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

Answer 2

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!

Answer 3

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?

 

Answer 4

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

Answer 5

Thanks Ashwani, that worked out great!