I am using SQL Server 2012 on Windows 7.
我在Windows 7上使用SQL Server 2012。
I am a beginner in SQL; I currently have this trigger on a table DeviceStatus:
我是SQL的初学者;我目前在表DeviceStatus上有这个触发器：
ALTER TRIGGER [dbo].[trigger_insertInStatus]
ON [dbo].[DeviceStatus]
AFTER INSERT
AS
BEGIN
-- Insert statements for trigger here:
INSERT INTO GeneralStatus (DeviceIP, ServiceStatus)
VALUES
( (SELECT DeviceIP FROM INSERTED), (SELECT ServiceStatus FROM INSERTED) );
END
where DeviceIP is a unique key.
其中DeviceIP是唯一键。
Problem is that, obviously, it crashes when trying to insert a record/row with a DeviceIP value that - accidentally - already exists in the second table named GeneralStatus...
问题是，显然，它在尝试插入具有DeviceIP值的记录/行时崩溃 - 意外 - 已存在于名为GeneralStatus的第二个表中...
How should I modify/adapt the above SQL query in order to keep the existing functionality (INSERT INTO...), but adding also the possibility to have the ServiceStatus column updated in such a case when the DeviceIP value already exists in the GeneralStatus table?
What's the best way to write this (performant, yet safe)?
我应该如何修改/调整上述SQL查询以保留现有功能（INSERT INTO ...），但是当GeneralIPatus表中已存在DeviceIP值时，还可以添加更新ServiceStatus列的可能性？写这个的最好方法是什么（高效，安全）？
2 个解决方案
#1
2
Since Inserted has multiple rows, each of which can be an insert or update to GeneralStatus, you can join with inserted and check based on the row exists or not like this.
由于Inserted有多行，每一行都可以插入或更新到GeneralStatus，你可以加入插入和检查基于存在或不存在的行。
ALTER TRIGGER [dbo].[trigger_insertInStatus]
ON [dbo].[DeviceStatus]
AFTER INSERT
AS
BEGIN
--Update Records where DeviceIP does exist in GeneralStatus
UPDATE GS
SET GS.ServiceStatus = I.ServiceStatus
FROM GeneralStatus GS INNER JOIN Inserted I ON GS.DeviceIP = I.DeviceIP
-- Insert statements for trigger here:
-- Insert records where DeviceIP does not exist in GeneralStatus
INSERT INTO GeneralStatus
( DeviceIP,ServiceStatus )
SELECT DeviceIP , ServiceStatus FROM INSERTED I
LEFT JOIN GeneralStatus GS ON GS.DeviceIP = I.DeviceIP
WHERE GS.DeviceIP IS NULL;
END
#2
2
INSERT INTO GeneralStatus
( DeviceIP, ServiceStatus )
SELECT DeviceIP ,ServiceStatus FROM INSERTED I
where not exists(select 1 from generalstatus g where g.deviceip=i.deviceip)
#1
2
Since Inserted has multiple rows, each of which can be an insert or update to GeneralStatus, you can join with inserted and check based on the row exists or not like this.
由于Inserted有多行，每一行都可以插入或更新到GeneralStatus，你可以加入插入和检查基于存在或不存在的行。
ALTER TRIGGER [dbo].[trigger_insertInStatus]
ON [dbo].[DeviceStatus]
AFTER INSERT
AS
BEGIN
--Update Records where DeviceIP does exist in GeneralStatus
UPDATE GS
SET GS.ServiceStatus = I.ServiceStatus
FROM GeneralStatus GS INNER JOIN Inserted I ON GS.DeviceIP = I.DeviceIP
-- Insert statements for trigger here:
-- Insert records where DeviceIP does not exist in GeneralStatus
INSERT INTO GeneralStatus
( DeviceIP,ServiceStatus )
SELECT DeviceIP , ServiceStatus FROM INSERTED I
LEFT JOIN GeneralStatus GS ON GS.DeviceIP = I.DeviceIP
WHERE GS.DeviceIP IS NULL;
END
#2
2
INSERT INTO GeneralStatus
( DeviceIP, ServiceStatus )
SELECT DeviceIP ,ServiceStatus FROM INSERTED I
where not exists(select 1 from generalstatus g where g.deviceip=i.deviceip)