Results 1 to 5 of 5

Thread: Trigger Problem

  1. #1
    Join Date
    Aug 2010
    Posts
    1

    Unanswered: Trigger Problem

    Where I made a mistake??

    I want to create trigger to prevent INSERT, UPDATE or DELETE statements being applied against SalesDispatchItem rows related to a SalesDispatch row with a status of “Sent”.



    create trigger dbo.PreventSentDispatchItemChange

    on dbo.SalesDispatchItem

    for update, insert , delete as

    declare @SalesDispatchID int

    declare @Status nvarchar(50)

    select @SalesDispatchID = SalesDispatchID from Inserted

    select @Status = status from SalesDispatch, SalesDispatchItem where SalesDispatchID = @SalesDispatchID and SalesDispatchID = SalesDispatchItem.SalesDispatchID

    if (@Status = 'Sent')

    BEGIN

    PRINT 'Details of a dispatch that has been sent cannot be updated!'

    ROLLBACK TRANSACTION

    END

    select @SalesDispatchID = SalesDispatchID from Deleted

    select @Status = status from SalesDispatch, SalesDispatchItem where SalesDispatchID = @SalesDispatchID and SalesDispatchID = SalesDispatchItem.SalesDispatchID

    if (@Status = 'Sent')

    BEGIN

    PRINT 'Details of a dispatch that has been sent cannot be updated!'

    ROLLBACK TRANSACTION

    END
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    CREATE TABLE SalesDispatch(
    	SalesDispatchID		INT NOT NULL,
    	Status			CHAR(5)	NOT NULL,
    	CONSTRAINT pk_SalesDispatch PRIMARY KEY (SalesDispatchID)
    )
    
    INSERT INTO SalesDispatch
    SELECT 1, 'Sent'
    	UNION ALL 
    SELECT 5, 'Open'
    
    
    CREATE TABLE SalesDispatchItem(
    	SalesDispatchItemId	INT NOT NULL,
    	SalesDispatchID		INT NOT NULL,
    	Qty			INT	NOT NULL,
    	CONSTRAINT pk_SalesDispatchItem PRIMARY KEY (SalesDispatchItemId),
    	CONSTRAINT SalesDispatchItem_SalesDispatch FOREIGN KEY (SalesDispatchID) REFERENCES SalesDispatch (SalesDispatchID)
    )
    
    INSERT INTO SalesDispatchItem
    SELECT 1, 1, 6
    UNION ALL
    SELECT 10, 5, 4
    
    GO
    create trigger dbo.PreventSentDispatchItemChange
    on dbo.SalesDispatchItem
    for update, insert, delete 
    as
    BEGIN
    	SET NOCOUNT ON
    	declare @NmbrSentInserted INT
    	declare @NmbrSentDeleted INT
    
    	select @NmbrSentInserted = COUNT(*) 
    	from Inserted 
    		INNER JOIN SalesDispatch ON
    			Inserted.SalesDispatchID = SalesDispatch.SalesDispatchID
    	WHERE SalesDispatch.status = 'Sent'
    
    	select @NmbrSentDeleted = COUNT(*) 
    	from Deleted 
    		INNER JOIN SalesDispatch ON
    			Deleted.SalesDispatchID = SalesDispatch.SalesDispatchID
    	WHERE SalesDispatch.status = 'Sent'
    
    	if (@NmbrSentInserted > 0 OR @NmbrSentDeleted > 0)
    	BEGIN
    		RAISERROR ('Details of a dispatch that has been sent, cannot be changed afterwards!', 16, 1);
    		ROLLBACK TRANSACTION
    		RETURN 
    	END
    END
    GO
    
    select * from SalesDispatchItem INNER JOIN SalesDispatch ON SalesDispatchItem.SalesDispatchID = SalesDispatch.SalesDispatchID
    
    -- should pass
    UPDATE SalesDispatchItem
    SET Qty = Qty * 2
    WHERE SalesDispatchItemId = 10
    
    -- should pass
    INSERT INTO SalesDispatchItem SELECT 11, 5, 10 UNION ALL SELECT 12, 5, 20
    
    -- should pass
    DELETE 
    FROM SalesDispatchItem
    WHERE SalesDispatchItemId = 11
    
    
    -- should fail
    -- The following UPDATE affects multiple records. Because it should fail in 1 record, 
    -- all the records will have to undergo a ROLLBACK.
    -- UPDATE SalesDispatchItem SET Qty = Qty * 2 
    
    -- should fail
    -- UPDATE SalesDispatchItem SET Qty = Qty * 2 WHERE SalesDispatchItemId = 1
    
    -- should fail
    -- INSERT INTO SalesDispatchItem VALUES(2, 1, 100)
    
    -- should fail
    -- DELETE FROM SalesDispatchItem WHERE SalesDispatchItemId = 1
    
    select * from SalesDispatchItem INNER JOIN SalesDispatch ON SalesDispatchItem.SalesDispatchID = SalesDispatch.SalesDispatchID
    
    DROP trigger dbo.PreventSentDispatchItemChange
    DROP TABLE SalesDispatchItem
    DROP TABLE SalesDispatch
    Make sure your trigger will also work correctly when it is fired after a change on multiple records. Your program should intercept the ERROR and deal with it (MessageBox to the user or whatever).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    MR.Wim
    its nice to see such new method in trigger if once the trigger has been set is there any way to collapse it or just dropping the table just for knowing
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    ... is there any way to collapse it ...
    If you mean drop the trigger:
    Code:
    DROP trigger dbo.PreventSentDispatchItemChange
    DROP TABLE SalesDispatchItem
    DROP TABLE SalesDispatch
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Thumbs up

    that's the perfection
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •