trigger after update

laroussi derbel 41 Reputation points
2021-10-14T11:25:30.863+00:00

I created this trigger and i wanted that every time that a "start-value" change from false to true it insert a new line on thetable , so i decided to create a positive edge so that it no add a hudge numbre of lines the whole time that this "start-value" stays at true , but only one time . but apparently it dont work now, even if this start value change from false to true every hour )

ALTER TRIGGER [dbo].[tinsertV15]

ON [dbo].[OfenbuchVC1212_V10]

AFTER UPDATE

As

DECLARE @Prozessstart integer;

IF (SELECT dbo.TBL_LiveData_Bit.Value

FROM dbo.TBL_LiveData_Bit

WHERE dbo.TBL_LiveData_Bit.ConfigID = 251) =0

BEGIN

set @Prozessstart =0

END

IF ((SELECT dbo.TBL_LiveData_Bit.Value

FROM dbo.TBL_LiveData_Bit

WHERE dbo.TBL_LiveData_Bit.ConfigID = 251) =1) AND @Prozessstart < 1

BEGIN

set @Prozessstart = @Prozessstart + 1

END

IF @Prozessstart = 1

begin

INSERT INTO OfenbuchVC1212_V10 ( 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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 116.5K Reputation points MVP
    2021-10-15T21:06:25.09+00:00

    Maybe it should be

    IF EXISTS (SELECT *
               FROM   inserted i
               JOIN   deleted d ON i.ConfigID = d.ConfigID
               WHERE  i.ConfigID = 251
                 AND  d.Value = 0
                 AND  i.Value = 1)
    BEGIN
       INSERT INTO OfenbuchVC1212_V10 ( Datum , Zeit, Temperatur , Oxidationszeit )
          ...
    END
    

    That table does not seem correctly designed. sql_variant is a type you use rarely. I don't see why columns called Datum, Zeit, Temperatur, Oxidationszeit or Anmerkung would be sql_variant. I would expect date, time, decimal, decimal and nvarchar respectively.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 116.5K Reputation points MVP
    2021-10-14T21:34:11.063+00:00

    The trigger looks funny, because it appears to work on the full table on every update.

    Normally in a trigger you work with two virtual tables inserted and deleted. inserted holds the row inserted by an INSERT statement, and in case of an UPDATE trigger, it holds the rows how they look after the update. The deleted table holds the rows deleted by an DELETE statement, and for an UPDATE statement, deleted holds the rows as they looked like before the UPDATE statement.

    How they should be applied in your trigger, I don't know since I frankly don't understand what you are trying to do. But you should probably use them (or at least inserted to restrict which rows you are working with.

    Keep in mind that a trigger fires once per statement, and a statement may affect zero rows, one row or many rows.

    1 person found this answer helpful.

  2. MelissaMa-MSFT 24,211 Reputation points
    2021-10-15T01:27:35.73+00:00

    Hi @laroussi derbel ,

    Welcome to Microsoft Q&A!

    We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data. We also need to see the expected result of the sample.

    Please refer to below simple example and check whether it is helpful. If it is helpful, please update your trigger accordingly.

    One: create two tables.

    create table test  
    (id int,  
    startvalue bit)  
      
    insert into test values  
    (1,0),  
    (2,1)  
      
    create table testhistory   
    (id int,  
    startvalue bit)  
    

    Two: create the triger:

    create trigger test_update_trigger  
    on test  
    after update   
    as   
    begin  
    	insert into testhistory   
    	select a.id,a.startvalue  
    	from inserted a  
    	inner join deleted b   
    	on a.id=b.id  
    	--deleted.startvalue means the original value  
    	--inserted.startvalue means the updated value  
    	where b.startvalue=0 and a.startvalue=1  
    end  
    

    Three: validate the trigger, the startvalue of first row changed from 0 to 1.

    update test  
    set startvalue=1   
    where id=1  
      
    select * from testhistory  
    

    Output:

    id	startvalue  
    1	1  
    

    Four: update the startvalue of first row to 1 again or update the one which is already true, no new row would be inserted into testhistory table.

    update test  
    set startvalue=1   
    where id=1  
      
    update test  
    set startvalue=1   
    where id=2  
      
    select * from testhistory  
    

    Output:

    id	startvalue  
    1	1  
    

    Best regards,
    Melissa


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.