Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

SQL Server Trigger for INSERT, UPDATE is not working

Hi Experts,

I have created a trigger on my table (FOR INSERT, UPDATE), when I tried to update one record only using below query, the trigger is working fine and give me the correct message:

Update y table
set id = 1 WHERE name = Jhon

Open in new window




But, When I tried to update that table based on another table "it will update more than 1 record using join" using below query it doesn't work.
update y
set y.column= x.column
from Y
    inner join x on
        x.id = y.id

Open in new window


So, how can I fix this issue.
Thanks a lot in advance.
0
Harreni
Asked:
Harreni
  • 2
  • 2
  • 2
  • +2
5 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, how can I fix this issue.
That depends on how you built your trigger. Without the trigger code we can't help you.
0
 
Pawan KumarDatabase ExpertCommented:
Trigger code pls?
0
 
DimitrisSenior Solution ArchitectCommented:
your trigger code should use the INSERTED and DELETED tables
So I assume that in your Trigger you are just reading the values from these tables in local variables (@)
If you are updating a table in batch you should use something like this

CREATE TRIGGER MyTrigger ON MyTable
FOR INSERT
AS
--for example insert into a secondary table
INSERT INTO SECONDTABLE (FIELD1,FIELD1)
SELECT FIELDx1, FIELDx2
FROM INSERTED
-------------------
If the trigger is insert  trigger you only have INSERTED (new values)
if is an Update trigger you have INSERTED (new values) and DELETED (Old Values)
and in case of a delete trigger you only have Deleted

Nevertheless, the trigger code would help even more :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
HarreniAuthor Commented:
ALTER TRIGGER [dbo].[TRG_ForInsertAndUpdate]  ON MyTable
FOR INSERT, UPDATE
 
AS

DECLARE @SID nvarchar(20)
DECLARE @CID nvarchar(10)
DECLARE @DID nvarchar(10)

SELECT @SID = SID from inserted
SELECT @CID = CID from inserted


BEGIN TRANSACTION
BEGIN TRY
	
		if (SELECT 
				  PID 
				  FROM tb_Dataset CMD
				  WHERE CMD.DID= @DID)= @CID
		 
		BEGIN
	
					RAISERROR(N'CID are Same As PID ', 11, 1);
					INSERT INTO [dbo].[tb_Log](SID,CID)
					Values (@SID, @CID)
			
		END

END TRY

BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
      IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
		PRINT ERROR_MESSAGE();
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In that case try to work with a cursor:
ALTER TRIGGER [dbo].[TRG_ForInsertAndUpdate]  ON MyTable
FOR INSERT, UPDATE
 
AS

DECLARE @SID nvarchar(20)
DECLARE @CID nvarchar(10)
DECLARE @DID nvarchar(10)

BEGIN TRANSACTION

DECLARE @inserted CURSOR FOR   
SELECT SID, CID
FROM inserted

OPEN @inserted  
  
FETCH NEXT FROM @inserted   
INTO @SID, @CID  

WHILE @@FETCH_STATUS = 0  
BEGIN  
	BEGIN TRY

		if (SELECT PID 
			FROM tb_Dataset CMD
			WHERE CMD.DID= @DID)= @CID
		 
		BEGIN
			RAISERROR(N'CID are Same As PID ', 11, 1);
			INSERT INTO [dbo].[tb_Log](SID,CID)
			Values (@SID, @CID)
		END

	END TRY
	FETCH NEXT FROM @inserted   
	INTO @SID, @CID  
END   
CLOSE @inserted;  
DEALLOCATE @inserted;  

BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
      IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
		PRINT ERROR_MESSAGE();
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;

Open in new window

0
 
DimitrisSenior Solution ArchitectCommented:
First of all your variable @DID is always null
you never assign  a value

For this I assume that @DID is actualy the @CID

ALTER TRIGGER [dbo].[TRG_ForInsertAndUpdate]  ON MyTable
FOR INSERT, UPDATE
 
AS

DECLARE @SID nvarchar(20)
DECLARE @CID nvarchar(10)
DECLARE @DID nvarchar(10) -- Where this is set???

SELECT @SID = SID from inserted
SELECT @CID = CID from inserted


BEGIN TRANSACTION 
-- In most cases the transaction should be initiated externally and not within the trigger 
-- because if you rollback here the data will be written to toyr main table
BEGIN TRY
		IF EXISTS (	SELECT	1
					FROM	tb_Dataset CMD
							INNER JOIN INSERTED InsCIDs
								ON CMD.DID = InsCIDs.CID
							INNER JOIN INSERTED InsSID
								ON CMD.PID = InsSID.SID
					)
		BEGIN
			RAISERROR(N'CID are Same As PID ', 11, 1);
			--The log statement is never executed because the code goes directly to CATCH
			INSERT INTO [dbo].[tb_Log](SID,CID)
			SELECT	DISTINCT InsSID.SID , InsCIDs.CID
			FROM	tb_Dataset CMD
					INNER JOIN INSERTED InsCIDs
						ON CMD.DID = InsCIDs.CID
					INNER JOIN INSERTED InsSID
						ON CMD.PID = InsSID.SID
		END
END TRY

BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
      IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
		PRINT ERROR_MESSAGE();
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  

Open in new window

0
 
Scott PletcherSenior DBACommented:
SQL Server only fires a trigger once per statement, not per row.  So triggers must be written to handle multiple updates at once.

You're never supposed to attempt to return data from a trigger, so the SELECT ERROR... and the PRINT statement need to be removed.

A trigger is already within a transaction, so any rollback would affect the entire statement and not just what the trigger did (SQL Server doesn't support truly nested transactions.)  So I've removed any references to TRANSACTION in the trigger.

[Final minor point: The "FOR INSERT, UPDATE" is older syntax, so I've replaced it with the more current "AFTER INSERT, UPDATE".]


SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER TRIGGER [dbo].[TRG_ForInsertAndUpdate]  
ON MyTable
AFTER INSERT, UPDATE

AS
SET NOCOUNT ON;

IF EXISTS(
    SELECT 1
    FROM inserted i
    INNER JOIN tb_Dataset CMD ON i.DID = CMD.DID AND i.CID = CMD.PID
)
BEGIN
          RAISERROR(N'CID(s) are Same As PID(s). ', 11, 1);
          INSERT INTO [dbo].[tb_Log](SID,CID)
          SELECT i.SID, i.CID
          FROM inserted i
          INNER JOIN tb_Dataset CMD ON i.DID = CMD.DID AND i.CID = CMD.PID
END /*IF*/
GO
0
 
Scott PletcherSenior DBACommented:
I propose the last comment, my comment:

https://www.experts-exchange.com/questions/28979260/SQL-Server-Trigger-for-INSERT-UPDATE-is-not-working.html#a41862641

as the best, and only, solution, because all other solutions use variables in the trigger, and thus can't handle multiple rows being modified at once, but SQL Server always fires a trigger only once, no matter how many rows have been modified, so any solution that assumes that only a single row is being modified is not accurate.  Other solutions also attempt to return data and/or output from a trigger, which is also not allowed.
0
 
HarreniAuthor Commented:
Thanks a lot Experts.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now