July 22, 2020 at 2:52 am
Please help me convert the below trigger
(It was converted from an oracle DB and now needs to executed on local SQL DB. What trigger is trying to do- when a record is inserted or updated in TABLE A , find the corresponding record in TABLE B and update a column to a specific value)
CREATE OR REPLACE TRIGGER TR_SAMPLE
INSTEAD OF INSERT OR AS UPDATE
ON TABLE_A REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
@BEGIN
UPDATE TABLE B
SET @ACTIVE_FLAG = 'N'
WHERE ID = :NEW.ID
AND NAME = :NEW.NAME
AND SCHEMA = :NEW.SCHEMA
AND ACTIVE_FLAG != 'N'
AND TYPE dbo.IN ('B', 'E');
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
NULL;
END INS_TR_BATCH;
July 22, 2020 at 3:26 am
Something like below. It looks the original trigger is an INSTEAD OF INSERT and AFTER UPDATE at the same time. SQL Server doesn't allow you to define them together. If the trigger is intended to be INSERT OF INSERT, UPDATE then you just change the first trigger to handle both INSERT and UPDATE.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
IF EXISTS(SELECT 1 FROM sys.triggers WHERE name = 'TR_SAMPLE_INS')
DROP TRIGGER TR_SAMPLE_INS;
GO
CREATE TRIGGER TR_SAMPLE_INS
ON TABLE_A
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
BEGIN TRY
UPDATE B
SET ACTIVE_FLAG = 'N'
FROM [TABLE_B] B
INNER JOIN inserted i ON
B.ID = i.ID
AND B.NAME = i.NAME
AND B.[SCHEMA] = i.[SCHEMA]
AND B.ACTIVE_FLAG != 'N'
AND [B.TYPE] IN ('B', 'E')
END TRY
BEGIN CATCH
/*
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
NULL;
*/
END CATCH;
/*end of trigger*/
GO
IF EXISTS(SELECT 1 FROM sys.triggers WHERE name = 'TR_SAMPLE_UPD')
DROP TRIGGER TR_SAMPLE_UPD;
GO
CREATE TRIGGER TR_SAMPLE_UPD
ON TABLE_A
AFTER UPDATE
AS
SET NOCOUNT ON;
BEGIN TRY
UPDATE B
SET ACTIVE_FLAG = 'N'
FROM [TABLE_B] B
INNER JOIN inserted i ON
B.ID = i.ID
AND B.NAME = i.NAME
AND B.[SCHEMA] = i.[SCHEMA]
AND B.ACTIVE_FLAG != 'N'
AND [B.TYPE] IN ('B', 'E')
END TRY
BEGIN CATCH
/*
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
NULL;
*/
END CATCH;
/*end of trigger*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 22, 2020 at 4:44 am
hello, thanks for your reply. I tried to execute this trigger however table B is not being updated... the trigger ran fine. but when i insert values into Table A, table B is not being updated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply