rewriting this cursor ?

[sqlcasey]

Hi,

I was wondering if someone had any suggestions as to how to make this work without a cursor.

Thanks much

create TRIGGER [tgr_ExportHomesExceptionsHistory]
ON [dbo].[ExportHomesExceptions]
FOR DELETE, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @HomeIdDeleted int
	DECLARE HomeIdDeleted_Cursor Cursor For
		select HomeId from deleted
	OPEN HomeIdDeleted_Cursor
	FETCH NEXT FROM HomeIdDeleted_Cursor
	INTO @HomeIdDeleted
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		INSERT INTO ExportHomesExceptionsHistory (HomeId, CustId, LpPartnerCode,
		DateAdd, DateMod, ActionBy, Locked)
		(Select HomeId, CustId, LpPartnerCode, DateAdd, GetDate(),ActionBy, Locked 
		from deleted where HomeId=@HomeIdDeleted)
		
        IF (SELECT HomeId FROM deleted WHERE LpPartnerCode = 'ReAffinity'
		OR LpPartnerCode = 'Edgeio' AND HomeId=@HomeIdDeleted) <> 0 
		BEGIN
			UPDATE Homes Set ChgDate = GetDate() where HomeId = @HomeIdDeleted
		END
		FETCH NEXT FROM HomeIdDeleted_Cursor
		INTO @HomeIdDeleted			
	END
	CLOSE HomeIdDeleted_Cursor
	DEALLOCATE HomeIdDeleted_Cursor
END

 

[SQLDenis]

This should do it ...untested of course

create TRIGGER [tgr_ExportHomesExceptionsHistory]
ON [dbo].[ExportHomesExceptions]
FOR DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

        INSERT INTO ExportHomesExceptionsHistory (HomeId, CustId, LpPartnerCode,
        DateAdd, DateMod, ActionBy, Locked)
        Select HomeId, CustId, LpPartnerCode, DateAdd, GetDate(),ActionBy, Locked 
        from deleted 
        
      
            UPDATE h Set ChgDate = GetDate() 
		from Homes h Join deleted d on h.HomeId = d.HomeId 
		WHERE d.LpPartnerCode = 'ReAffinity'
        	OR d.LpPartnerCode = 'Edgeio'
		
END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

create TRIGGER [tgr_ExportHomesExceptionsHistory]
ON [dbo].[ExportHomesExceptions]
FOR DELETE, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @HomeIdDeleted int
    SELECT @HomeIdDeleted = MIN(HomeId) from deleted
    WHILE @HomeIdDeleted IS NOT NULL
    BEGIN
        INSERT INTO ExportHomesExceptionsHistory (HomeId, CustId, LpPartnerCode,
        DateAdd, DateMod, ActionBy, Locked)
        (Select HomeId, CustId, LpPartnerCode, DateAdd, GetDate(),ActionBy, Locked
        from deleted where HomeId=@HomeIdDeleted)
        
        IF (SELECT HomeId FROM deleted WHERE LpPartnerCode = 'ReAffinity'
        OR LpPartnerCode = 'Edgeio' AND HomeId=@HomeIdDeleted) <> 0
        BEGIN
            UPDATE Homes Set ChgDate = GetDate() where HomeId = @HomeIdDeleted
        END
        SELECT @HomeIdDeleted = MIN(HomeId) from deleted
               WHERE HomeId > @HomeIdDeleted
    END
END

not tested at all

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[sqlcasey]

Hi SQLDenis,

The first part of the trigger worked, but it completely ignored the UPDATE portion that uses the join.

Any ideas how to get that part to work?

Thanks much

 

[sqlcasey]

Sqldenis, my mistake - it works perfectly! Thanks much