November 23, 2015 at 2:08 pm
Hello All:
I have a trigger that I can't seem to find the issue. When I do an Update on the table, it still inserts a record as my logic to determine if the record exists doesn't seem to work.
The way this table is designed isn't the best, so please bear with me. We have a "log" table that takes gets inserted a ProjectID, DateTime stamp and two columns with NULL for TimeExecuted and '0' for HasBeenExecuted, which get updated when the process runs every thirty minutes checking the "TimeExecuted" and "HasBeenExecuted" columns to know all new records, to copy the project information from production over to a "data warehouse".
The way it works now is this trigger fires every time a user updates a column on the project, so if they perform 25 updates in that thirty minute window, there will be 25 updates on the same project, when in reality, only one is required as it simply copies all information, not just a column that was changed (or record inserted / deleted). This process really bogs down the server so I am trying to check if a record exists for a project and is waiting to be processed. if it does, do not insert a new record into this table but my trigger doesn't seem to be working as I had hoped and looking for some help as to what I am doing wrong. Pasted below is a copy of the trigger
CREATE TRIGGER [dbo].[ToDataWarehouse_Custom_Fields_] ON [dbo].[Custom_Fields] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-----------------------------------------------------------------------------------------------
--Check if records were changed
-----------------------------------------------------------------------------------------------
IF @@ROWCOUNT = 0 -- exit trigger when zero records affected
BEGIN
RETURN;
END;
-----------------------------------------------------------------------------------------------
--Variable Declarations
-----------------------------------------------------------------------------------------------
DECLARE @Cinfo VARBINARY(128)
DECLARE @ProjectID INT
DECLARE @ProjectType VARCHAR(50)
DECLARE @TableName VARCHAR(50)
-----------------------------------------------------------------------------------------------
--Prevent trigger from being executed for current process
-----------------------------------------------------------------------------------------------
SELECT @Cinfo = Context_Info()
IF @Cinfo = 0x55555
RETURN
-----------------------------------------------------------------------------------------------
--Check for Updated / Deleted or Inserted
-----------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM DELETED)
BEGIN
---------------------------------------------------------------------------------------
--Record has been updated or deleted
---------------------------------------------------------------------------------------
SET @ProjectID = (SELECT DELETED.Parent_Project_ID FROM DELETED)
SET @ProjectType = (SELECT Project_Type FROM DELETED JOIN Project_Information i ON DELETED.Parent_Project_ID = i.Project_ID)
SET @TableName = 'Custom_Fields'
---------------------------------------------------------------------------------------
--Check if record exists in update_log table that has not been processed for the given project
---------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM update_log WHERE Project_ID = @ProjectID AND [Project_Type] = @ProjectType AND [table_name] = @TableName AND [updated] = 0 AND updated_DW IS NULL)
BEGIN
-----------------------------------------------------------------------------------
--Insert record
-----------------------------------------------------------------------------------
INSERT INTO update_log
(project_id,
project_type,
table_name)
SELECT
DISTINCT
i.Project_ID,
i.Project_Type,
'Custom_Fields'
FROM
(SELECT Parent_Project_ID FROM DELETED) x
JOIN Project_Information i ON x.Parent_Project_ID = i.Project_ID
END
END
ELSE
BEGIN
---------------------------------------------------------------------------------------
--Record has been Inserted
---------------------------------------------------------------------------------------
SET @ProjectID = (SELECT INSERTED.Parent_Project_ID FROM INSERTED)
SET @ProjectType = (SELECT Project_Type FROM INSERTED JOIN Project_Information i ON INSERTED.Parent_Project_ID = i.Project_ID)
SET @TableName = 'Custom_Fields'
---------------------------------------------------------------------------------------
--Check if record exists in update_log table that has not been processed for the given project
---------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM update_log WHERE Project_ID = @ProjectID AND [Project_Type] = @ProjectType AND [table_name] = @TableName AND [updated] = 0 AND updated_DW IS NULL)
BEGIN
---------------------------------------------------------------------------------------
--Insert will always be a new record
---------------------------------------------------------------------------------------
INSERT INTO update_log
(project_id,
project_type,
table_name)
SELECT
DISTINCT
i.Project_ID,
i.Project_Type,
'Custom_Fields'
FROM
(SELECT Parent_Project_ID FROM INSERTED) x
JOIN Project_Information i ON x.Parent_Project_ID = i.Project_ID
END
END
END
November 23, 2015 at 2:32 pm
DECLARE @DML CHAR(6) =
CASE WHEN
EXISTS(SELECT 1 FROM inserted)
AND EXISTS(SELECT 1 FROM deleted)
THEN 'Update'
WHEN
EXISTS(SELECT 1 FROM inserted)
THEN 'Insert'
WHEN
EXISTS(SELECT 1 FROM deleted)
THEN 'Delete'
ELSE NULL
END
IF @DML = 'Delete'
BEGIN
DELETE ...
FROM ...
JOIN deleted d ...
RETURN
END
IF @DML = 'Insert'
BEGIN
INSERT ...
SELECT ...
FROM inserted
RETURN
END
IF @DML = 'Update'
BEGIN
UPDATE ...
FROM inserted i
JOIN ...
RETURN
END
November 23, 2015 at 2:48 pm
The biggest issue with your trigger is that it is relying on scalar values. This is bug red flag because in sql server, triggers fire once per operation and scalar variable indicate the code expects there to be only a single value. Your code seems to be suffering from this quite a bit. Triggers MUST be set based to function correctly.
We can help you here we need some more details. First and foremost, what is this trigger attempting to do? Any additional information like schema, sample data will go a long way towards getting assistance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 23, 2015 at 2:50 pm
Looking a bit closer you could save yourself a LOT of headaches by breaking this into three triggers just to start. One each for INSERT, DELETE, UPDATE. At least that way you don't all your code piled on top of each other.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply