Link to home
Start Free TrialLog in
Avatar of Harreni
HarreniFlag for Saudi Arabia

asked on

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.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

So, how can I fix this issue.
That depends on how you built your trigger. Without the trigger code we can't help you.
Trigger code pls?
SOLUTION
Avatar of Dimitris
Dimitris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Harreni

ASKER

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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Harreni

ASKER

Thanks a lot Experts.