October 18, 2021 at 1:15 pm
Hello,
I am looking for a way whereby a trigger will be created ONLY when a particular column of a table is updated. Take for instance, I have a table with about 5 columns including start column. I want a situation whenever start is updated(from false to true ), some data will be inserted in another table which I have already created.
I can create a trigger after the update on a table generally, but in this case, I want only the new table to be updated only when the start column is updated. How do I go about this please?
i tried this programm and it didnt work
Create TRIGGER [dbo].[tinsertV71]
ON [dbo].[TBL_LiveData_Bit]
AFTER UPDATE
As
DECLARE
@Prozessstart integer = 0
,@liveDataValue integer ;
SELECT @liveDataValue = dbo.TBL_LiveData_Bit.Value
FROM dbo.TBL_LiveData_Bit
WHERE dbo.TBL_LiveData_Bit.ConfigID = 251; -- this is the column that change from false to true
IF @liveDataValue =0
BEGIN
SET @Prozessstart = 0 ;
END
IF @liveDataValue =1 AND @Prozessstart < 1
BEGIN
set @Prozessstart = @Prozessstart + 1
BEGIN
INSERT INTO OfenbuchVC1212_V71 ( Datum , Zeit, Temperatur , Oxidationszeit )
SELECT dbo.V_LiveData.Value , V_LiveData_1.Value , V_LiveData_2.Value , V_LiveData_3.Value
FROM dbo.V_LiveData CROSS JOIN
dbo.V_LiveData AS V_LiveData_1 CROSS JOIN
dbo.V_LiveData AS V_LiveData_2 CROSS JOIN
dbo.V_LiveData AS V_LiveData_3
WHERE (dbo.V_LiveData.ConfigID = 159)AND (V_LiveData_1.ConfigID = 253) AND (V_LiveData_2.ConfigID = 141) AND (V_LiveData_3.ConfigID = 140)
END
END
October 18, 2021 at 2:13 pm
Create your trigger as you normally would do but add the additional columns_updated() function to specify when it should fire for what columns you want to to update for the trigger to fire.
Either that or build logic to check the inserted and deleted tables for the column where the values are different then fire the rest of the trigger code.
October 18, 2021 at 2:17 pm
thank your for your answer , zes thats the problem i didnt know yher should i set the condition fo fire the trigger , where exactly ? after the "after Update" ?
October 18, 2021 at 2:40 pm
You need to incorporate the virtual tables "inserted" and "deleted" into you query.
Also, when you update the "start" column, why can't you also add code to set the the columns?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 19, 2021 at 11:10 am
thank you for your answer and your time. i really appreciate it .
i tried it like this
ALTER TRIGGER [dbo].[tinsertV86]
ON [dbo].[TBL_LiveData_Bit]
AFTER UPDATE
AS
IF EXISTS (SELECT *
FROM inserted i
JOIN deleted d ON i.ConfigID = d.ConfigID
WHERE i.ConfigID = 117
AND d.Value = 0
AND i.Value = 1)
BEGIN
INSERT INTO OfenbuchVC1212_V86 ( Datum , Zeit, Temperatur , Oxidationszeit )
SELECT dbo.V_LiveData.Value , V_LiveData_1.Value , V_LiveData_2.Value , V_LiveData_3.Value
FROM dbo.V_LiveData CROSS JOIN
dbo.V_LiveData AS V_LiveData_1 CROSS JOIN
dbo.V_LiveData AS V_LiveData_2 CROSS JOIN
dbo.V_LiveData AS V_LiveData_3
WHERE (dbo.V_LiveData.ConfigID = 159)AND (V_LiveData_1.ConfigID = 253) AND (V_LiveData_2.ConfigID = 141) AND (V_LiveData_3.ConfigID = 140)
END
but the trigger didnt fire although the "ConfigID = 117" shows "true " right now
October 19, 2021 at 12:58 pm
Did the trigger not fire, or did the code not execute?
To test if a single column was updated, use the function UPDATE()
Then, if true, use the values of inserted and updated to insert a new record.
Again, same question. Why are you using a trigger to do this, as opposed to adding code to whatever process?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply