oracle触发器翻译为符合sqlserver数据库语法的触发器

图1为oracle表结构

图2为sqlserver表结构

**id,version是SYN_SECSPEC表的字段，ruleid,ruleversion是ALARMHIST_RUN表中的字段。id是主键且自增的，ruleid非自增
以下为各字段详情
**

CREATE OR REPLACE TRIGGER TR_ALARMHIST_RUN BEFORE INSERT OR UPDATE ON ALARMHIST_RUN  FOR EACH ROW
DECLARE
V_COUNT NUMBER:=0;
V_RUN   WAFERROUTE_RUN%rowtype;
V_SECSPEC SYN_SECSPEC%rowtype;
V_ALIASNAME VARCHAR2(64):='';
BEGIN

:NEW.DATETIME1:=GETPARTITIONValue(:NEW.DATETIME);

SELECT COUNT(ID) INTO  V_COUNT FROM WAFERROUTE_RUN WHERE RUNID=:NEW.RUNID;


IF(V_COUNT=1) THEN
SELECT * INTO  V_RUN FROM WAFERROUTE_RUN WHERE RUNID=:NEW.RUNID;
    :NEW.LAYER:=V_RUN.LAYER;
    :NEW.LOTID:=V_RUN.LOTID;
    :NEW.WAFERID:=V_RUN.WAFERID;
    :NEW.PRODUCT:=V_RUN.PRODUCT;
    :NEW.OPERNO:=V_RUN.OPERNO;
    :NEW.TOOLRECIPE:=V_RUN.TOOLRECIPE;
    :NEW.LOTRUNID:=V_RUN.LOTRUNID;
SELECT * INTO V_SECSPEC FROM  SYN_SECSPEC WHERE id=:NEW.RULEID AND version=:NEW.RULEVERSION;
SELECT ALIASNAME INTO V_ALIASNAME FROM  SYN_EQ_PARANAME WHERE EQID=:NEW.EQID AND PARANAME=:NEW.PARANAME AND CHAMBERID=:NEW.CHAMBERID;
:NEW.ALIASNAME:=V_ALIASNAME;
:NEW.RULENAME:=V_SECSPEC.methodname;
select decode(:new.alarmseverity,'OUTLIER',V_SECSPEC.OUTLIER_ACTION,'ALARM',V_SECSPEC.ALARM_ACTION,'WARNING',V_SECSPEC.WARN_ACTION,'NA') into :NEW.ALARMCODE from dual;

ELSIF(V_COUNT>1) THEN

NULL;
END IF;

DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID=:NEW.ID;
INSERT INTO ALARMHIST_WAFERROUTE_RUN
(
ID,
RUNID,
DATETIME,
EQID,
CHAMBERID,
RECIPEID,
PARANAME,
ALGORITHM,
ALARMSEVERITY,
ALARMTYPE,
ACTION,
RULEID,
RULEVERSION,
ALARMVALUE,
DATETIME1,
MEMO,
INSTIME,
MEMOTIME,
RAWID,
FORMULA,
ALARMCONDS,
LAYER,
LOTID,
WAFERID,
ALIASNAME,
RULENAME,
ALARMCODE,
REPORTED,
PRODUCT,
OPERNO,
TOOLRECIPE,
LOTRUNID,
ERROR
)
SELECT
:NEW.ID,
:NEW.RUNID,
:NEW.DATETIME,
:NEW.EQID,
:NEW.CHAMBERID,
:NEW.RECIPEID,
:NEW.PARANAME,
:NEW.ALGORITHM,
:NEW.ALARMSEVERITY,
:NEW.ALARMTYPE,
:NEW.ACTION,
:NEW.RULEID,
:NEW.RULEVERSION,
:NEW.ALARMVALUE,
:NEW.DATETIME1,
:NEW.MEMO,
:NEW.INSTIME,
:NEW.MEMOTIME,
:NEW.RAWID,
:NEW.FORMULA,
:NEW.ALARMCONDS,
R.LAYER,
R.LOTID,
R.WAFERID,
P.ALIASNAME,
S.methodname AS RULENAME,
decode(:new.ALARMSEVERITY,'OUTLIER',S.OUTLIER_ACTION,'ALARM',S.ALARM_ACTION,'WARNING',S.WARN_ACTION,'NA') AS ALARMCODE,
:NEW.REPORTED,
R.PRODUCT,
R.OPERNO,
R.TOOLRECIPE,
R.LOTRUNID,
:NEW.ERROR
 FROM
(SELECT R.* FROM WAFERROUTE_RUN R WHERE R.EQID=:NEW.EQID AND R.RUNID=:NEW.RUNID AND R.CHAMBERID=:NEW.CHAMBERID) R,
SYN_SECSPEC S, SYN_EQ_PARANAME P
WHERE S.id=:NEW.RULEID AND S.version=:NEW.RULEVERSION AND
P.EQID=:NEW.EQID AND P.PARANAME=:NEW.PARANAME AND P.CHAMBERID=:NEW.CHAMBERID;

EXCEPTION
WHEN OTHERS THEN NULL;

END;

CREATE TRIGGER TR_ALARMHIST_RUN
ON ALARMHIST_RUN
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE
@V_COUNT INT = 0,
@V_RUN WAFERROUTE_RUN,
@V_SECSPEC SYN_SECSPEC,
@V_ALIASNAME VARCHAR(64)='';
SET NOCOUNT ON;

UPDATE ALARMHIST_RUN SET DATETIME1 = GETPARTITIONValue(inserted.DATETIME)
FROM ALARMHIST_RUN INNER JOIN inserted ON ALARMHIST_RUN.ID = inserted.ID;

SELECT @V_COUNT = COUNT(ID) FROM WAFERROUTE_RUN WHERE RUNID = inserted.RUNID;

IF(@V_COUNT = 1)
BEGIN
    SELECT TOP 1 @V_RUN.* FROM WAFERROUTE_RUN @V_RUN WHERE RUNID = inserted.RUNID;
    UPDATE ALARMHIST_RUN SET 
        LAYER = @V_RUN.LAYER,
        LOTID = @V_RUN.LOTID,
        WAFERID = @V_RUN.WAFERID,
        PRODUCT = @V_RUN.PRODUCT,
        OPERNO = @V_RUN.OPERNO,
        TOOLRECIPE = @V_RUN.TOOLRECIPE,
        LOTRUNID = @V_RUN.LOTRUNID
    FROM ALARMHIST_RUN
    INNER JOIN inserted ON ALARMHIST_RUN.ID = inserted.ID;

    SELECT TOP 1 @V_SECSPEC.* FROM SYN_SECSPEC @V_SECSPEC WHERE id = inserted.RULEID AND version = inserted.RULEVERSION;
    SELECT TOP 1 @V_ALIASNAME = ALIASNAME FROM SYN_EQ_PARANAME WHERE EQID = inserted.EQID AND PARANAME = inserted.PARANAME AND CHAMBERID = inserted.CHAMBERID;
    UPDATE ALARMHIST_RUN SET 
        ALIASNAME = @V_ALIASNAME,
        RULENAME = @V_SECSPEC.methodname,
        ALARMCODE = 
            CASE inserted.ALARMSEVERITY 
                WHEN 'OUTLIER' THEN @V_SECSPEC.OUTLIER_ACTION 
                WHEN 'ALARM' THEN @V_SECSPEC.ALARM_ACTION 
                WHEN 'WARNING' THEN @V_SECSPEC.WARN_ACTION 
                ELSE 'NA' 
            END
    FROM ALARMHIST_RUN
    INNER JOIN inserted ON ALARMHIST_RUN.ID = inserted.ID;
END

IF(@V_COUNT > 1)
BEGIN
    DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID = inserted.ID;
    INSERT INTO ALARMHIST_WAFERROUTE_RUN
    (
        ID,
        RUNID,
        DATETIME,
        EQID,
        CHAMBERID,
        RECIPEID,
        PARANAME,
        ALGORITHM,
        ALARMSEVERITY,
        ALARMTYPE,
        ACTION,
        RULEID,
        RULEVERSION,
        ALARMVALUE,
        DATETIME1,
        MEMO,
        INSTIME,
        MEMOTIME,
        RAWID,
        FORMULA,
        ALARMCONDS,
        LAYER,
        LOTID,
        WAFERID,
        ALIASNAME,
        RULENAME,
        ALARMCODE,
        REPORTED,
        PRODUCT,
        OPERNO,
        TOOLRECIPE,
        LOTRUNID,
        ERROR
    )
    SELECT 
        inserted.ID,
        inserted.RUNID,
        inserted.DATETIME,
        inserted

以下是将Oracle触发器转换为符合SQL Server数据库语法的触发器的示例代码：

CREATE TRIGGER TR_ALARMHIST_RUN
ON ALARMHIST_RUN
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE
@V_COUNT INT = 0,
@V_ALIASNAME VARCHAR(64) = '',
@V_RUN WAFERROUTE_RUN,
@V_SECSPEC SYN_SECSPEC;

SET NOCOUNT ON;

UPDATE ALARMHIST_RUN
SET DATETIME1 = dbo.GETPARTITIONValue(INSERTED.DATETIME)
FROM INSERTED
WHERE ALARMHIST_RUN.ID = INSERTED.ID;

SELECT @V_COUNT = COUNT(ID) FROM WAFERROUTE_RUN WHERE RUNID = INSERTED.RUNID;

IF (@V_COUNT = 1)
BEGIN
SELECT TOP 1 @V_RUN = * FROM WAFERROUTE_RUN WHERE RUNID = INSERTED.RUNID;
UPDATE ALARMHIST_RUN SET
LAYER = @V_RUN.LAYER,
LOTID = @V_RUN.LOTID,
WAFERID = @V_RUN.WAFERID,
PRODUCT = @V_RUN.PRODUCT,
OPERNO = @V_RUN.OPERNO,
TOOLRECIPE = @V_RUN.TOOLRECIPE,
LOTRUNID = @V_RUN.LOTRUNID
FROM INSERTED
WHERE ALARMHIST_RUN.ID = INSERTED.ID;

SELECT TOP 1 @V_SECSPEC = * FROM SYN_SECSPEC WHERE id = INSERTED.RULEID AND version = INSERTED.RULEVERSION;
SELECT @V_ALIASNAME = ALIASNAME FROM SYN_EQ_PARANAME WHERE EQID = INSERTED.EQID AND PARANAME = INSERTED.PARANAME AND CHAMBERID = INSERTED.CHAMBERID;

UPDATE ALARMHIST_RUN SET
    ALIASNAME = @V_ALIASNAME,
    RULENAME = @V_SECSPEC.methodname,
    ALARMCODE = CASE INSERTED.ALARMSEVERITY
                    WHEN 'OUTLIER' THEN @V_SECSPEC.OUTLIER_ACTION
                    WHEN 'ALARM' THEN @V_SECSPEC.ALARM_ACTION
                    WHEN 'WARNING' THEN @V_SECSPEC.WARN_ACTION
                    ELSE 'NA'
                END
FROM INSERTED
WHERE ALARMHIST_RUN.ID = INSERTED.ID;

END

DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID = INSERTED.ID;

INSERT INTO ALARMHIST_WAFERROUTE_RUN
(
ID,
RUNID,
DATETIME,
EQID,
CHAMBERID,
RECIPEID,
PARANAME,
ALGORITHM,
ALARMSEVERITY,
ALARMTYPE,
ACTION,
RULEID,
RULEVERSION,
ALARMVALUE,
DATETIME1,
MEMO,
INSTIME,
MEMOTIME,
RAWID,
FORMULA,
ALARMCONDS,
LAYER,
LOTID,
WAFERID,
ALIASNAME,
RULENAME,
ALARMCODE,
REPORTED,
PRODUCT,
OPERNO,
TOOLRECIPE,
LOTRUNID,
ERROR
)
SELECT
INSERTED.ID,
INSERTED.RUNID,
INSERTED.DATETIME,
INSERTED.EQID,
INSERTED.CHAMBERID,
INSERTED.RECIPEID,
INSERTED.PARANAME,
INSERTED.ALGORITHM,
INSERTED.ALARMSEVERITY,

答案出自数据库 https://www.wodianping.com/

参考GPT和自己的思路：在SQL Server中创建相应的触发器，可能需要对代码进行一些修改和调整，以适应SQL Server的语法和功能。

首先，在SQL Server中，触发器需要与特定的表相关联，因此我们需要确定在哪个表上创建触发器。假设我们将这个触发器与名为“ALARMHIST_RUN”的表相关联。

在SQL Server中，触发器必须定义在BEGIN和END之间，因此我们需要将整个PL/SQL块放置在BEGIN和END之间，并对代码进行一些语法调整。例如，在SQL Server中，每个语句必须以分号结尾。

CREATE TRIGGER TR_ALARMHIST_RUN
ON ALARMHIST_RUN
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @V_COUNT INT;
DECLARE @V_RUN WAFERROUTE_RUN;
DECLARE @V_SECSPEC SYN_SECSPEC;
DECLARE @V_ALIASNAME VARCHAR(64);
SET @V_COUNT = 0;
SET @V_ALIASNAME = '';

IF EXISTS(SELECT * FROM inserted)
BEGIN
    SET NOCOUNT ON;

    UPDATE ALARMHIST_RUN 
    SET DATETIME1 = GETPARTITIONValue(inserted.DATETIME)
    FROM inserted
    WHERE ALARMHIST_RUN.ID = inserted.ID;

    SELECT @V_COUNT = COUNT(ID) FROM WAFERROUTE_RUN WHERE RUNID = inserted.RUNID;

    IF @V_COUNT = 1
    BEGIN
        SELECT @V_RUN = * FROM WAFERROUTE_RUN WHERE RUNID = inserted.RUNID;

        UPDATE ALARMHIST_RUN
        SET LAYER = @V_RUN.LAYER,
            LOTID = @V_RUN.LOTID,
            WAFERID = @V_RUN.WAFERID,
            PRODUCT = @V_RUN.PRODUCT,
            OPERNO = @V_RUN.OPERNO,
            TOOLRECIPE = @V_RUN.TOOLRECIPE,
            LOTRUNID = @V_RUN.LOTRUNID
        FROM inserted
        WHERE ALARMHIST_RUN.ID = inserted.ID;

        SELECT @V_SECSPEC = * FROM SYN_SECSPEC WHERE id = inserted.RULEID AND version = inserted.RULEVERSION;

        SELECT @V_ALIASNAME = ALIASNAME FROM SYN_EQ_PARANAME WHERE EQID = inserted.EQID AND PARANAME = inserted.PARANAME AND CHAMBERID = inserted.CHAMBERID;

        UPDATE ALARMHIST_RUN
        SET ALIASNAME = @V_ALIASNAME,
            RULENAME = @V_SECSPEC.methodname,
            ALARMCODE = 
            CASE inserted.ALARMSEVERITY
                WHEN 'OUTLIER' THEN @V_SECSPEC.OUTLIER_ACTION
                WHEN 'ALARM' THEN @V_SECSPEC.ALARM_ACTION
                WHEN 'WARNING' THEN @V_SECSPEC.WARN_ACTION
                ELSE 'NA'
            END
        FROM inserted
        WHERE ALARMHIST_RUN.ID = inserted.ID;
    END
    ELSE IF @V_COUNT > 1
    BEGIN
        -- do nothing
    END

    DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID = inserted.ID;

    INSERT INTO ALARMHIST_WAFERROUTE_RUN
    (
        ID,
        RUNID,
        DATETIME,
        EQID,
        CHAMBERID,
        RECIPEID,
        PARANAME,
        ALGORITHM,
        ALARMSEVERITY,
ALARMTYPE,
ACTION,
RULEID,
RULEVERSION,
ALARMVALUE,
DATETIME1,
MEMO,
INSTIME,
MEMOTIME,
RAWID,
FORMULA,
ALARMCONDS,
LAYER,
LOTID,
WAFERID,
ALIASNAME,
RULENAME,
ALARMCODE,
REPORTED,
PRODUCT,
OPERNO,
TOOLRECIPE,
LOTRUNID,
ERROR
)
SELECT
NEW.ID,
NEW.RUNID,
NEW.DATETIME,
NEW.EQID,
NEW.CHAMBERID,
NEW.RECIPEID,
NEW.PARANAME,
NEW.ALGORITHM,
NEW.ALARMSEVERITY,
NEW.ALARMTYPE,
NEW.ACTION,
NEW.RULEID,
NEW.RULEVERSION,
NEW.ALARMVALUE,
GETPARTITIONValue(NEW.DATETIME),
NEW.MEMO,
NEW.INSTIME,
NEW.MEMOTIME,
NEW.RAWID,
NEW.FORMULA,
NEW.ALARMCONDS,
R.LAYER,
R.LOTID,
R.WAFERID,
P.ALIASNAME,
S.methodname AS RULENAME,
CASE NEW.ALARMSEVERITY
WHEN 'OUTLIER' THEN S.OUTLIER_ACTION
WHEN 'ALARM' THEN S.ALARM_ACTION
WHEN 'WARNING' THEN S.WARN_ACTION
ELSE 'NA'
END AS ALARMCODE,
NEW.REPORTED,
R.PRODUCT,
R.OPERNO,
R.TOOLRECIPE,
R.LOTRUNID,
NEW.ERROR
FROM WAFERROUTE_RUN R
INNER JOIN SYN_SECSPEC S ON S.id = NEW.RULEID AND S.version = NEW.RULEVERSION
INNER JOIN SYN_EQ_PARANAME P ON P.EQID = NEW.EQID AND P.PARANAME = NEW.PARANAME AND P.CHAMBERID = NEW.CHAMBERID
WHERE R.EQID = NEW.EQID AND R.RUNID = NEW.RUNID AND R.CHAMBERID = NEW.CHAMBERID;
AND R.LOTRUNID = NEW.LOTRUNID;

EXCEPTION
WHEN OTHERS THEN
NULL;

END;

该回答引用GPTᴼᴾᴱᴺᴬᴵ
以下是将Oracle触发器转换为符合SQL Server数据库语法的触发器的示例代码：

CREATE TRIGGER TR_ALARMHIST_RUN
ON ALARMHIST_RUN
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @V_COUNT INT
DECLARE @V_RUN WAFERROUTE_RUN%ROWTYPE
DECLARE @V_SECSPEC SYN_SECSPEC%ROWTYPE
DECLARE @V_ALIASNAME VARCHAR(64)
SET @V_COUNT = 0
SET @V_ALIASNAME = ''

UPDATE ALARMHIST_RUN
SET DATETIME1 = dbo.GETPARTITIONValue(INSERTED.DATETIME)
WHERE ID = INSERTED.ID

SELECT @V_COUNT = COUNT(ID) FROM WAFERROUTE_RUN WHERE RUNID = INSERTED.RUNID;

IF (@V_COUNT = 1)
BEGIN
    SELECT TOP 1 @V_RUN = * FROM WAFERROUTE_RUN WHERE RUNID = INSERTED.RUNID;

    UPDATE ALARMHIST_RUN
    SET LAYER = @V_RUN.LAYER,
        LOTID = @V_RUN.LOTID,
        WAFERID = @V_RUN.WAFERID,
        PRODUCT = @V_RUN.PRODUCT,
        OPERNO = @V_RUN.OPERNO,
        TOOLRECIPE = @V_RUN.TOOLRECIPE,
        LOTRUNID = @V_RUN.LOTRUNID
    WHERE ID = INSERTED.ID

    SELECT TOP 1 @V_SECSPEC = * FROM SYN_SECSPEC WHERE id = INSERTED.RULEID AND version = INSERTED.RULEVERSION;

    SELECT TOP 1 @V_ALIASNAME = ALIASNAME FROM SYN_EQ_PARANAME WHERE EQID = INSERTED.EQID AND PARANAME = INSERTED.PARANAME AND CHAMBERID = INSERTED.CHAMBERID;

    UPDATE ALARMHIST_RUN
    SET ALIASNAME = @V_ALIASNAME,
        RULENAME = @V_SECSPEC.methodname,
        ALARMCODE = CASE INSERTED.alarmseverity
                        WHEN 'OUTLIER' THEN @V_SECSPEC.OUTLIER_ACTION
                        WHEN 'ALARM' THEN @V_SECSPEC.ALARM_ACTION
                        WHEN 'WARNING' THEN @V_SECSPEC.WARN_ACTION
                        ELSE 'NA'
                    END
    WHERE ID = INSERTED.ID
END

DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID = INSERTED.ID;

INSERT INTO ALARMHIST_WAFERROUTE_RUN (
    ID,
    RUNID,
    DATETIME,
    EQID,
    CHAMBERID,
    RECIPEID,
    PARANAME,
    ALGORITHM,
    ALARMSEVERITY,
    ALARMTYPE,
    ACTION,
    RULEID,
    RULEVERSION,
    ALARMVALUE,
    DATETIME1,
    MEMO,
    INSTIME,
    MEMOTIME,
    RAWID,
    FORMULA,
    ALARMCONDS,
    LAYER,
    LOTID,
    WAFERID,
    ALIASNAME,
    RULENAME,
    ALARMCODE,
    REPORTED,
    PRODUCT,
    OPERNO,
    TOOLRECIPE,
    LOTRUNID,
    ERROR
)
SELECT
    INSERTED.ID,
    INSERTED.RUNID,
    INSERTED.DATETIME,
    INSERTED.EQID,
    INSERTED.CHAMBERID,
    INSERTED.RECIPEID,
    INSERTED.PARANAME,
    INSERTED.ALGORITHM,
    INSERTED.ALARMSEVERITY,
INSERTED.ALARMTYPE,
INSERTED.ACTION,
INSERTED.RULEID,
INSERTED.RULEVERSION,
INSERTED.ALARMVALUE,
INSERTED.DATETIME1,
INSERTED.MEMO,
INSERTED.INSTIME,
INSERTED.MEMOTIME,
INSERTED.RAWID,
INSERTED.FORMULA,
INSERTED.ALARMCONDS,
R.LAYER,
R.LOTID,
R.WAFERID,
P.ALIASNAME,
S.methodname AS RULENAME,
CASE INSERTED.ALARMSEVERITY
WHEN 'OUTLIER' THEN S.OUTLIER_ACTION
WHEN 'ALARM' THEN S.ALARM_ACTION
WHEN 'WARNING' THEN S.WARN_ACTION
ELSE 'NA'
END AS ALARMCODE,
INSERTED.REPORTED,
R.PRODUCT,
R.OPERNO,
R.TOOLRECIPE,
R.LOTRUNID,
INSERTED.ERROR
FROM
(
SELECT
ID,
RUNID,
EQID,
CHAMBERID,
RECIPEID,
PARANAME,
ALGORITHM,
ALARMSEVERITY,
ALARMTYPE,
ACTION,
RULEID,
RULEVERSION,
ALARMVALUE,
DATETIME,
MEMO,
INSTIME,
MEMOTIME,
RAWID,
FORMULA,
ALARMCONDS,
LAYER,
LOTID,
WAFERID,
DATETIME1,
ALIASNAME,
REPORTED,
ERROR
FROM INSERTED
) INSERTED
JOIN WAFERROUTE_RUN R ON R.EQID = INSERTED.EQID AND R.RUNID = INSERTED.RUNID AND R.CHAMBERID = INSERTED.CHAMBERID
JOIN SYN_SECSPEC S ON S.id = INSERTED.RULEID AND S.version = INSERTED.RULEVERSION
JOIN SYN_EQ_PARANAME P ON P.EQID = INSERTED.EQID AND P.PARANAME = INSERTED.PARANAME AND P.CHAMBERID = INSERTED.CHAMBERID;

参考GPT的回答和自己的思路，以下是转换后的SQL Server触发器：

CREATE TRIGGER TR_ALARMHIST_RUN ON ALARMHIST_RUN
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @V_COUNT INT = 0;
DECLARE @V_RUN WAFERROUTE_RUN;
DECLARE @V_SECSPEC SYN_SECSPEC;
DECLARE @V_ALIASNAME VARCHAR(64) = '';
-- Get partition value
UPDATE ALARMHIST_RUN SET DATETIME1 = dbo.GETPARTITIONValue(DATETIME) WHERE ID IN (SELECT ID FROM inserted);

-- Query WAFERROUTE_RUN table to get count of RUNID
SELECT @V_COUNT = COUNT(ID) FROM WAFERROUTE_RUN WHERE RUNID IN (SELECT RUNID FROM inserted);

IF(@V_COUNT = 1)
BEGIN
    -- Get WAFERROUTE_RUN record and set values in ALARMHIST_RUN table
    SELECT TOP 1 @V_RUN = * FROM WAFERROUTE_RUN WHERE RUNID IN (SELECT RUNID FROM inserted);
    UPDATE ALARMHIST_RUN SET LAYER = @V_RUN.LAYER, LOTID = @V_RUN.LOTID, WAFERID = @V_RUN.WAFERID, PRODUCT = @V_RUN.PRODUCT, OPERNO = @V_RUN.OPERNO, TOOLRECIPE = @V_RUN.TOOLRECIPE, LOTRUNID = @V_RUN.LOTRUNID WHERE ID IN (SELECT ID FROM inserted);
    
    -- Get SYN_SECSPEC record to set RULENAME and ALARMCODE
    SELECT TOP 1 @V_SECSPEC = * FROM SYN_SECSPEC WHERE id IN (SELECT RULEID FROM inserted) AND version IN (SELECT RULEVERSION FROM inserted);
    UPDATE ALARMHIST_RUN SET RULENAME = @V_SECSPEC.methodname, ALARMCODE = 
        CASE :new.alarmseverity
            WHEN 'OUTLIER' THEN @V_SECSPEC.OUTLIER_ACTION
            WHEN 'ALARM' THEN @V_SECSPEC.ALARM_ACTION
            WHEN 'WARNING' THEN @V_SECSPEC.WARN_ACTION
            ELSE 'NA'
        END
    WHERE ID IN (SELECT ID FROM inserted);
    
    -- Get SYN_EQ_PARANAME record to set ALIASNAME
    SELECT TOP 1 @V_ALIASNAME = ALIASNAME FROM SYN_EQ_PARANAME WHERE EQID IN (SELECT EQID FROM inserted) AND PARANAME IN (SELECT PARANAME FROM inserted) AND CHAMBERID IN (SELECT CHAMBERID FROM inserted);
    UPDATE ALARMHIST_RUN SET ALIASNAME = @V_ALIASNAME WHERE ID IN (SELECT ID FROM inserted);
END

IF(@V_COUNT > 1)
BEGIN
    -- Do nothing
END

-- Delete from ALARMHIST_WAFERROUTE_RUN table
DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID IN (SELECT ID FROM inserted);

-- Insert into ALARMHIST_WAFERROUTE_RUN table
INSERT INTO ALARMHIST_WAFERROUTE_RUN
(
    ID,
    RUNID,
    DATETIME,
    EQID,
    CHAMBERID,
    RECIPEID,
    PARANAME,
    ALGORITHM,
    ALARMSEVERITY,
    ALARMTYPE,
    ACTION,
    RULEID,
    RULEVERSION,
    ALARMVALUE,
    DATETIME1,
    MEMO,
    INSTIME,
    MEMOTIME,
    RAWID,
    FORMULA,
    ALARMCONDS,
    LAYER,
    LOTID,
    WAFERID,
    ALIASNAME,
    RULENAME,
    ALARMCODE,
    REPORTED,
    PRODUCT,
    OPERNO,
    TOOLRECIPE,
    LOTRUNID,
    ERROR
)
SELECT
inserted.ID,
inserted.RUNID,
inserted.DATETIME,
inserted.EQID,
inserted.CHAMBERID,
inserted.RECIPEID,
inserted.PARANAME,
inserted.ALGORITHM,
inserted.ALARMSEVERITY,
inserted.ALARMTYPE,
inserted.ACTION,
inserted.RULEID,
inserted.RULEVERSION,
inserted.ALARMVALUE,
inserted.DATETIME1,
inserted.MEMO,
inserted.INSTIME,
inserted.MEMOTIME,
inserted.RAWID,
inserted.FORMULA,
inserted.ALARMCONDS,
R.LAYER,
R.LOTID,
R.WAFERID,
P.ALIASNAME,
S.methodname AS RULENAME,
CASE inserted.ALARMSEVERITY
WHEN 'OUTLIER' THEN S.OUTLIER_ACTION
WHEN 'ALARM' THEN S.ALARM_ACTION
WHEN 'WARNING' THEN S.WARN_ACTION
ELSE 'NA'
END AS ALARMCODE,
inserted.REPORTED,
R.PRODUCT,
R.OPERNO,
R.TOOLRECIPE,
R.LOTRUNID,
inserted.ERROR
FROM inserted
JOIN (
SELECT R.*
FROM WAFERROUTE_RUN R
WHERE R.EQID=inserted.EQID AND R.RUNID=inserted.RUNID AND R.CHAMBERID=inserted.CHAMBERID
) R ON 1=1
JOIN SYN_SECSPEC S ON S.id=inserted.RULEID AND S.version=inserted.RULEVERSION
JOIN SYN_EQ_PARANAME P ON P.EQID=inserted.EQID AND P.PARANAME=inserted.PARANAME AND P.CHAMBERID=inserted.CHAMBERID;

END;

回答不易，还请采纳！！！

笑死我了，凡是带有 declare @v_run WAFERROUTE_RUN的，一律都是错的

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
将Oracle触发器转换为SQL Server触发器的一般步骤如下：

1. 将Oracle的PL/SQL代码转换为SQL Server的T-SQL代码。
2. 根据SQL Server的语法规则进行调整，例如将Oracle的rowtype转换为SQL Server的具体类型。
3. 根据SQL Server的数据库结构进行调整，例如修改表名、字段名、数据类型等。

以下是将给出的Oracle触发器转换为SQL Server触发器的代码：

CREATE TRIGGER TR_ALARMHIST_RUN
ON ALARMHIST_RUN
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @V_COUNT INT;
    DECLARE @V_RUN WAFERROUTE_RUN;
    DECLARE @V_SECSPEC SYN_SECSPEC;
    DECLARE @V_ALIASNAME VARCHAR(64)='';

    SELECT @V_COUNT = COUNT(ID)
    FROM WAFERROUTE_RUN
    WHERE RUNID=INSERTED.RUNID;

    IF (@V_COUNT=1)
    BEGIN
        SELECT @V_RUN=* 
        FROM WAFERROUTE_RUN
        WHERE RUNID=INSERTED.RUNID;

        UPDATE ALARMHIST_RUN
        SET LAYER=@V_RUN.LAYER,
            LOTID=@V_RUN.LOTID,
            WAFERID=@V_RUN.WAFERID,
            PRODUCT=@V_RUN.PRODUCT,
            OPERNO=@V_RUN.OPERNO,
            TOOLRECIPE=@V_RUN.TOOLRECIPE,
            LOTRUNID=@V_RUN.LOTRUNID
        FROM ALARMHIST_RUN
        WHERE ID=INSERTED.ID;

        SELECT @V_SECSPEC=*
        FROM SYN_SECSPEC
        WHERE id=INSERTED.RULEID AND version=INSERTED.RULEVERSION;

        SELECT @V_ALIASNAME=ALIASNAME
        FROM SYN_EQ_PARANAME
        WHERE EQID=INSERTED.EQID AND PARANAME=INSERTED.PARANAME AND CHAMBERID=INSERTED.CHAMBERID;

        UPDATE ALARMHIST_RUN
        SET ALIASNAME=@V_ALIASNAME,
            RULENAME=@V_SECSPEC.methodname,
            ALARMCODE=IIF(INSERTED.ALARMSEVERITY='OUTLIER',@V_SECSPEC.OUTLIER_ACTION,IIF(INSERTED.ALARMSEVERITY='ALARM',@V_SECSPEC.ALARM_ACTION,IIF(INSERTED.ALARMSEVERITY='WARNING',@V_SECSPEC.WARN_ACTION,'NA')))
        FROM ALARMHIST_RUN
        WHERE ID=INSERTED.ID;

    END;
    ELSE IF (@V_COUNT>1)
    BEGIN
        -- Do nothing
    END;

    DELETE FROM ALARMHIST_WAFERROUTE_RUN WHERE ID=INSERTED.ID;

    INSERT INTO ALARMHIST_WAFERROUTE_RUN (
        ID,
        RUNID,
        DATETIME,
        EQID,
        CHAMBERID,
        RECIPEID,
        PARANAME,
        ALGORITHM,
        ALARMSEVERITY,
        ALARMTYPE,
        ACTION,
        RULEID,
        RULEVERSION,
        ALARMVALUE,
        DATETIME1,
        MEMO,
        INSTIME,
        MEMOTIME,
        RAWID,
        FORMULA,
        ALARMCONDS,
        LAYER,
        LOTID,
        WAFERID,
        ALIASNAME,
        RULENAME,
        ALARMCODE,
        REPORTED,
        PRODUCT,
        OPERNO,
        TOOLRECIPE,
        LOTRUNID,
        ERROR
    )
    SELECT 
        INSERTED.ID,
        INSERTED.RUNID,
        INSERTED.DATETIME,
        INSERTED.EQID,
        INSERTED.CHAMBERID,
        INSERTED.RECIPEID,
        INSERTED.PARANAME,
        INSERTED.ALGORITHM,
        INSERTED.ALARMSEVERITY,
        INSERTED.ALARMTYPE,
        INSERTED.ACTION,
        INSERTED.RULEID,
        INSERTED.RULEVERSION,
        INSERTED.ALARMVALUE,
        INSERTED.DATETIME1,
        INSERTED.MEMO,
        INSERTED.INSTIME,
        INSERTED.MEMOTIME,
        INSERTED.RAWID,
        INSERTED.FORMULA,
        INSERTED.ALARMCONDS,
        R.LAYER,
        R.LOTID,
        R.WAFERID,
        P.ALIASNAME,
        S.methodname AS RULENAME,
        IIF(INSERTED.ALARMSEVERITY='OUTLIER',S.OUTLIER_ACTION,IIF(INSERTED.ALARMSEVERITY='ALARM',S.ALARM_ACTION,IIF(INSERTED.ALARMSEVERITY='WARNING',S.WARN_ACTION,'NA'))) AS ALARMCODE,
        INSERTED.REPORTED,
        R.PRODUCT,
        R.OPERNO,
        R.TOOLRECIPE,
        R.LOTRUNID,
        INSERTED.ERROR
    FROM (
        SELECT R.* FROM WAFERROUTE_RUN R WHERE R.EQID=INSERTED.EQID AND R.RUNID=INSERTED.RUNID AND R.CHAMBERID=INSERTED.CHAMBERID
    ) R,
    SYN_SECSPEC S,
    SYN_EQ_PARANAME P
    WHERE S.id=INSERTED.RULEID AND S.version=INSERTED.RULEVERSION AND
          P.EQID=INSERTED.EQID AND P.PARANAME=INSERTED.PARANAME AND P.CHAMBERID=INSERTED.CHAMBERID;

END;

代码中的一些说明：

• 每个触发器都被SQL Server视为存储过程，因此在代码开头应该有BEGIN和END语句。
• 使用IIF()函数替代Oracle中的decode()函数。
• 使用：OLD和：NEW替代Oracle中的OLD和NEW伪记录。
• 在SQL Server中，ROWTYPE类型不可用。因此，需要使用具体的变量类型。在这个例子中，使用了与表相同的变量类型。
  如果我的回答解决了您的问题，请采纳！

• 这篇博客也许可以解决你的问题👉 ：ORACLE和sqlServer查出表所有的触发器及触发器详细信息