December 23, 2015 at 9:05 am
I have two databases, DB_MAIN and DB_FRONT. Both have a similar table setup:
CREATE TABLE dbo.tbl_RF_Questions(
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[AnswerId] [varchar](36) NULL,
[Question] [nvarchar](256) NOT NULL,
[Answer] [nvarchar](2048) NULL,
[DateAsked] [smalldatetime] NULL,
[DateAnswered] [smalldatetime] NULL,
)
The issue comes at updating the table on the front end. I'm looking into triggers but am wary of implementing it. I have one written for an update only.
CREATE TRIGGER dbo.tr_Question_Details_Update
ON dbo.tbl_RF_Questions
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--IF UPDATE(movie_id) checks if a particular column was updated
-- Insert statements for trigger here
DECLARE @Count int
DECLARE @QuestionID int
SELECT @QuestionID = QuestionID
FROM INSERTED
SELECT @Count = Count(QUestionID)
FROM DB_MAIN.dbo.tbl_RF_Questions
WHERE QuestionID = @QuestionID;
IF @Count >0
BEGIN
UPDATE CS
SET
CS.[ProgramID] = M.ProgramID
,CS.[StatusID] = M.StatusID
,CS.[AnswerId] = M.AnswerId
,CS.[Question] = M.Question
,CS.[Answer] = M.Answer
,CS.[DateAsked] = M.DateAsked
,CS.[DateAnswered] = M.DateAnswered
FROM DB_FRONT.dbo.tbl_RF_Questions CS
JOIN DB_MAIN.dbo.tbl_RF_Questions M JOIN
DB_MAIN.dbo.tbl_RF_Users U ON M.AnswerID=U.ID
ON CS.QuestionID = M.QuestionID
WHERE CS.QuestionID = @QuestionID;
END
END
GO
The scenario is that questions get inserted on the main db and updated/inserted on the front db when some changes that particular record in main. Haven't written the insertion portion, just the update. On rare occurrences, I might need to do a bulk update of multiple records, so if i do that, would the trigger run multiple times or would it run in one instance? How would it affect performance?
December 23, 2015 at 9:21 am
The trigger fires once per update, not once per row updated. It will have an adverse effect on performance if you use a loop instead of a set-based update. You might find it's more efficient to use the Inserted and Deleted virtual tables in the UPDATE statement in your trigger instead of the table on which the trigger is created.
John
December 23, 2015 at 11:19 am
CREATE TRIGGER dbo.tr_Question_Details_Update
ON dbo.tbl_RF_Questions
AFTER UPDATE
AS
--if no rows affected, exit.
IF @@ROWCOUNT = 0
RETURN;
SET NOCOUNT ON;
UPDATE CS
SET [ProgramID] = M.ProgramID,
[StatusID] = M.StatusID,
[AnswerId] = M.AnswerId,
[Question] = M.Question,
[Answer] = M.Answer,
[DateAsked] = M.DateAsked,
[DateAnswered] = M.DateAnswered
FROM DB_FRONT.dbo.tbl_RF_Questions CS
INNER JOIN inserted i ON CS.QuestionID = i.QuestionID
JOIN DB_MAIN.dbo.tbl_RF_Questions M ON CS.QuestionID = M.QuestionID
JOIN DB_MAIN.dbo.tbl_RF_Users U ON M.AnswerID=U.ID
GO --end of trigger
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 23, 2015 at 1:49 pm
Thanks for both of your responses. Like I said it's, incomplete. I'm going to have to update it so that to insert records to the front table if they are not in that table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply