Harreni
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:
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.
So, how can I fix this issue.
Thanks a lot in advance.
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
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
So, how can I fix this issue.
Thanks a lot in advance.
Trigger code pls?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot Experts.