插入触发器:需要将传入的字符串拆分为两个不同的字段

Question

我的数据库由连接到PLC的kepware填充。我有一个kepware标记名“H-T1XX2.disdo_PLC.ST 10 Cycle Timer”，我想根据句点(“”)来拆分它。"H-T1XX2‘= WorkCenter和"St 10 Cycle Timer“=站点。如有任何帮助，将不胜感激。

寻找从表中拉出字符串并拆分字符串的帮助。

USE [StationCycleTime]
GO
/****** Object:  Trigger [MES].[plcPRODInsert]    Script Date: 1/24/2020 11:04:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[plcInsert]
ON [dbo].[tbl_CycleTime]
AFTER INSERT
AS
BEGIN
    declare @WC varchar(10)
    declare @ST varchar(20)
    declare @InStr varchar(50)
    declare @MaxID varchar(50)

    SELECT @MaxID = n.ID
    FROM [dbo].[tbl_CycleTime] n
    WHERE n.[ID] = MAX(ID)

    SELECT @InStr = m.Station
    FROM [dbo].[tbl_CycleTime] m
    WHERE m.[ID] = @MaxID

    SET @WC = (SELECT PARSENAME(@InStr,3))
    SET @ST = (SELECT PARSENAME(@InStr,1))

    SET NOCOUNT ON;
    UPDATE  [dbo].[tbl_CycleTime]
    SET [WorkCenter] = @WC, [Station] = @ST 
    WHERE [ID] = (
        SELECT MAX(ID) from [dbo].[tbl_CycleTime])
END

Error I am receiving

Answer 1

你可以试试这个。要获取表的最大id，可以使用SQL Server中的Top。我不确定它将如何工作。

下面的行给出了Id字段的最大值。

SELECT TOP 1 ID from [dbo].[tbl_CycleTime] ORDER BY ID DESC

因此，请尝试以下替代方案。

 UPDATE  [dbo].[tbl_CycleTime]
    SET [WorkCenter] = @WC, [Station] = @ST 
    WHERE [ID] = (
        SELECT TOP 1 ID from [dbo].[tbl_CycleTime] ORDER BY ID DESC)

触发器最棒的地方在于它提供了两个名为inserted和deleted的魔术表。您应该使用插入的表来获取的最大id -即插入和删除的值，如下所示。

WHERE [ID] = inserted.ID -- To get the inserted id values.

以下是SQL Server中inserted and deleted Tables的文档。

Answer 2

明白了，谢谢你的帮助。

BEGIN
    declare @WC varchar(10)
    declare @ST varchar(20)
    declare @InStr varchar(50)

SELECT @InStr = m.Station
FROM [dbo].[tbl_CycleTime] m
WHERE m.[ID] = (
    SELECT TOP 1 ID from [dbo].[tbl_CycleTime] ORDER BY ID DESC)

SET @WC = (SELECT PARSENAME(@InStr,3))
SET @ST = (SELECT PARSENAME(@InStr,1))

SET NOCOUNT ON;
UPDATE  [dbo].[tbl_CycleTime]
SET [WorkCenter] = @WC, [Station] = @ST 
WHERE [ID] = (
    SELECT MAX(ID) from [dbo].[tbl_CycleTime])
END