Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Trigger erroring becuase of multiple values?

    Alright, This has me stumped. I have a simple trigger to update another table's timestamp when there is a change to its related table. Here is the trigger.

    ALTER TRIGGER [dbo].[Eco_Data_Timestamp]
    ON [dbo].[ECO_DATA]
    FOR INSERT, UPDATE
    AS
    DECLARE @EGUID uniqueidentifier
    SELECT @EGUID = (SELECT TOP 1 E_GUID FROM Inserted)
    BEGIN
    UPDATE ECO SET E_Modified = getdate() WHERE E_GUID = @EGUID
    END

    I have tried to change the select method, however I dont think it matters. I have ran it so that it simply displayed the data instead of setting EGUID against it and I am only getting one result. The error I am getting is this:

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    And the code I am executing is this, one line:

    INSERT INTO Eco_Data (ED_GUID,ED_Item,ED_Type,ED_RevTo,ED_Title,ED_Chan ge,E_GUID,PL_GUID,ED_DocID) VALUES ('7324de7b-aee6-4ffe-a527-4c5acb3bd20d','1','SOP','A','Test','Initial Release','88afa8b7-bc5d-410e-a031-913e82a5dade','','001')

    is there something that I am missing?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You should write triggers always in a way that they still function properly when the trigger is fired by an action on one or on multiple rows.
    Code:
    DROP TABLE DaTable
    DROP TABLE DaOtherTable
    
    CREATE TABLE DaTable(
    	id		bigint	identity (1, 1)	not null,
    	column1		CHAR(10)	NOT NULL,
    	CONSTRAINT PK_DaTable PRIMARY KEY(id)
    )
    
    CREATE TABLE DaOtherTable(
    	id		bigint	identity (1, 1)	not null,
    	DaTableId	bigint	NOT NULL,
    	ModifiedOn	datetime,
    	CONSTRAINT PK_DaOtherTable PRIMARY KEY(id)
    )
    GO
    
    CREATE TRIGGER [dbo].[DaTable_Timestamp]
    ON dbo.DaTable
    FOR INSERT, UPDATE
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	UPDATE DaOtherTable 
    	SET ModifiedOn = GetDate()
    	FROM Inserted
    	WHERE DaOtherTable.DaTableId = Inserted.Id
    
    	INSERT INTO DaOtherTable(DaTableId, ModifiedOn)
    	SELECT Inserted.Id, GetDate()
    	From Inserted
    	WHERE NOT EXISTS (SELECT 1
    			FROM DaOtherTable
    			WHERE DaOtherTable.DaTableId = Inserted.Id
    			)
    
    	SET NOCOUNT OFF
    END
    
    SELECT * from DaTable
    SELECT * from DaOtherTable
    
    INSERT INTO DaTable (column1) VALUES('Hi')
    INSERT INTO DaTable (column1) VALUES('How')
    update DaTable SET column1 = 'Hello,' WHERE Id = 1
    
    INSERT INTO DaTable (column1) 
    SELECT 'are' UNION ALL
    SELECT 'you' UNION ALL
    SELECT 'today' UNION ALL
    SELECT '?'
    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
    Dec 2009
    Posts
    2
    Thank you for your response. I mainly code in VB.Net and have not been using SQL Triggers for a long time. I am familiar with loops to loop through something similar to a dataset but I am confused how the code you provided acts on all of the rows provided. Can you please tell me what statements are allowing for this so I can do some research on this?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    put your cursor on update in your query window and hit F1. Then go to google and type in "sql set based processing". Read everything.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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