none
Trigger Update/Insert Max Record on table RRS feed

  • Question

  •  

    Ok I'll start this off with saying it's been quite some time since I've programmed in TSQL. The situation is as follows. I have two tables in SQL Server 2008 the first of which stores information on a unique record (foreign key) and the second of which is meant to keep a historical log of any changes. The table designs are as follows:

    dbo.tblAssigntments

    Assignments_ID (int, not null)

    Acct# (nvarchar(255), not null)

    MASTER_ID (int, null)

    UNIQUE_ID (nvarchar(255), null)

    AS_EmpID (int, null)

    MS_EmpID (int, null)

    dbo.tblAssigntmentsHistory

    AssignmentsHist_ID (int, not null)

    Acct# (nvarchar(255), not null)

    MASTER_ID (int, null)

    UNIQUE_ID (nvarchar(255), null)

    AS_EmpID (int, null)

    MS_EmpID (int, null)

    DateAssigned (date, null)

    DateReassigned (date, null)

    My plan was to develop a trigger on dbo.tblAssignments that would update the DateReassigned field on the max record of dbo.tblAssignmentsHistory for that MASTER_ID with the current date. It would then append a new record with the new data to dbo.tblAssignmentsHistory. I may be making this too complicated. As is when I run an append or update query on dbo.tblAssignments the query just hangs and eventually times out. My code is as follows:

    USE [MyDB]
    GO
    /****** Object:  Trigger [dbo].[trg_AssignmentsHistory_AS]    Script Date: 03/21/2012 14:21:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER TRIGGER [dbo].[trg_AssignmentsHistory_AS]
       ON  [dbo].[tblAssignments] 
    AFTER INSERT, UPDATE 
    AS 
    DECLARE @masterID as INT, @ASEmpID as INT, @blnInsert as BIT 
    DECLARE TrigTempInsert_Cursor CURSOR FOR 
    SELECT MASTER_ID, AS_EmpID
    FROM inserted;
    	--SET @masterID = inserted.MASTER_ID
    	--SET @ASEmpID = inserted.AS_EmpID
    	
    OPEN TrigTempInsert_Cursor;
    FETCH NEXT FROM TrigTempInsert_Cursor INTO @masterID, @ASEmpID
    WHILE @@FETCH_STATUS = 0
    If exists (select * from deleted)
    	--Update
    	BEGIN	
    		
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	SET @blnInsert = 0
    	
    	MERGE INTO MyDB.dbo.VW_MAX_AssignmentHistory AS TGT
    	USING (SELECT Acct#, UNIQUE_ID, MS_EmpID, AS_EmpID, MASTER_ID FROM MyDB.dbo.tblAssignments WHERE MASTER_ID =  @masterID) as SRC
    	ON TGT.MASTER_ID = SRC.MASTER_ID
    		WHEN MATCHED AND TGT.AS_EmpID <> @ASEmpID THEN
    		--Update and Insert
    		UPDATE SET TGT.DateReassigned = GetDate(),
    		@blnInsert = -1;
    	If @blnInsert = -1
    		INSERT INTO dbo.VW_MAX_AssignmentHistory (Acct#, MASTER_ID, UNIQUE_ID, AS_EmpID, MS_EmpID, DateAssigned)
    		Select SRC.Acct#, @masterID, SRC.UNIQUE_ID, @ASEmpID, SRC.MS_EmpID, GetDate()
    		From  MyDB.dbo.tblAssignments as SRC
    		Where MASTER_ID = @masterID
    	END;
    Else
    	--Insert
    	BEGIN	
    		
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    	INSERT INTO dbo.VW_MAX_AssignmentHistory (Acct#, MASTER_ID, UNIQUE_ID, AS_EmpID, MS_EmpID, DateAssigned)
    	Select SRC.Acct#, @masterID, SRC.UNIQUE_ID, @ASEmpID, SRC.MS_EmpID, GetDate()
    	From  MyDB.dbo.tblAssignments as SRC
    	Where MASTER_ID = @masterID
    	
    	END;
    CLOSE TrigTempInsert_Cursor ;
    DEALLOCATE  TrigTempInsert_Cursor ;

    FOOTNOTE: dbo.VW_MAX_AssignmentsHistory is a view I created to pull the max AssignmentsHist_ID for that particular MASTER_ID


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.

    Wednesday, March 21, 2012 6:30 PM

Answers

  • Try:

    ALTER TRIGGER [dbo].[trg_AssignmentsHistory_AS] ON [dbo].[tblAssignments] AFTER INSERT, UPDATE AS IF NOT EXISTS (select 1 from Inserted) RETURN ;-- no changes IF EXISTS (SELECT 1 FROM Deleted) -- Update on the Assignment table

    BEGIN ;with Changed as (select A.Assignment_History_ID, A.DateReassigned,

    ROW_NUMBER() OVER (PARTITION BY A.Master_ID ORDER BY DateAssigned DESC) as LastRec-- PK from dbo.AssignmentHistory A inner join Inserted I on A.Master_ID = I.Master_ID)

    UPDATE Changed SET DateReassigned = CURRENT_TIMESTAMP where LastRec = 1;

    END

    -- Always insert new rows / change rows

    INSERT INTO dbo.AssignmentHistory (....)

    SELECT ... FROM Inserted



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Wednesday, March 21, 2012 7:43 PM
    • Marked as answer by Don Mon Wednesday, March 21, 2012 8:13 PM
    Wednesday, March 21, 2012 6:44 PM
    Moderator

All replies

  • Try:

    ALTER TRIGGER [dbo].[trg_AssignmentsHistory_AS] ON [dbo].[tblAssignments] AFTER INSERT, UPDATE AS IF NOT EXISTS (select 1 from Inserted) RETURN ;-- no changes IF EXISTS (SELECT 1 FROM Deleted) -- Update on the Assignment table

    BEGIN ;with Changed as (select A.Assignment_History_ID, A.DateReassigned,

    ROW_NUMBER() OVER (PARTITION BY A.Master_ID ORDER BY DateAssigned DESC) as LastRec-- PK from dbo.AssignmentHistory A inner join Inserted I on A.Master_ID = I.Master_ID)

    UPDATE Changed SET DateReassigned = CURRENT_TIMESTAMP where LastRec = 1;

    END

    -- Always insert new rows / change rows

    INSERT INTO dbo.AssignmentHistory (....)

    SELECT ... FROM Inserted



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Wednesday, March 21, 2012 7:43 PM
    • Marked as answer by Don Mon Wednesday, March 21, 2012 8:13 PM
    Wednesday, March 21, 2012 6:44 PM
    Moderator
  • Hello I am recieving the following:

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 14

    The multi-part identifier "A.MASTER_ID" could not be bound.

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 12

    The multi-part identifier "A.AssignmentsHist_ID" could not be bound.

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 12

    The multi-part identifier "A.DateReassigned" could not be bound.

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 12

    The multi-part identifier "A.MASTER" could not be bound.


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.

    Wednesday, March 21, 2012 6:56 PM
  • Sorry, I got distracted to do some work... Ok, I see a typo in the above statement - this is very simple to find. Try to find it - I was showing you the idea.

    UPDATE. Never mind, I also decided to fix it. The above statement is fixed now.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Wednesday, March 21, 2012 7:42 PM
    Moderator
  • Thank you for the response. This is what I am actually seeing. Sorry for the typo I have been adjusting certain field names for privacy sake:

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 14

    The multi-part identifier "A.MASTER_ID" could not be bound.

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 12

    The multi-part identifier "A.AssignmentsHist_ID" could not be bound.

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 12

    The multi-part identifier "A.DateReassigned" could not be bound.

    Msg 4104, Level 16, State 1, Procedure trg_AssignmentsHistory_AS, Line 12

    The multi-part identifier "A.MASTER_ID" could not be bound.


    One must therefore be a fox to recognize traps, and a lion to frighten wolves.

    Wednesday, March 21, 2012 7:45 PM
  • I fixed the above statement. It was a typo - I meant to use alias H for history, but used A instead except for one place. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, March 21, 2012 7:47 PM
    Moderator
  • You are the best Naomi. Saved me hours.

    One must therefore be a fox to recognize traps, and a lion to frighten wolves.

    Wednesday, March 21, 2012 8:12 PM