positive edge

  • Hi every one , i wanted to build a positive edge with a variable that i named @Prozessstart. this is my programm

    Create TRIGGER [dbo].[tinsert]

    ON [dbo].[TBL_LiveData]

    AFTER UPDATE

    As

    DECLARE @Prozessstart integer;

    Prozessstart = 0

    IF (SELECT dbo.TBL_LiveData.Value

    FROM dbo.TBL_LiveData

    WHERE dbo.TBL_LiveData.ConfigID = 251) =0

    BEGIN

    @Prozessstart := 0 ;

    END

    IF ((SELECT dbo.TBL_LiveData.Value

    FROM dbo.TBL_LiveData

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

    BEGIN

    @Prozessstart <= @Prozessstart + 1

    INSERT INTO ....

    END

     

    but i recieve this error message ,

    Nachricht 102, Stufe 15, Status 1, Prozedur tinsertV9, Zeile 7 [Batchstartzeile 7]

    Falsche Syntax in der Nähe von "@Prozessstart".

    Nachricht 102, Stufe 15, Status 1, Prozedur tinsertV9, Zeile 12 [Batchstartzeile 7]

    Falsche Syntax in der Nähe von "@Prozessstart".

    Nachricht 102, Stufe 15, Status 1, Prozedur tinsertV9, Zeile 18 [Batchstartzeile 7]

    Falsche Syntax in der Nähe von "@Prozessstart".

    Nachricht 102, Stufe 15, Status 1, Prozedur tinsertV9, Zeile 26 [Batchstartzeile 7]

    Falsche Syntax in der Nähe von "END".

    can anyone please help me ?

  • The basic syntax to set a variable in T-SQL is

    SET @Prozessstart = 0

    Your example is missing the SET in the first two instances where you set your variable.

    I'm not sure what

    BEGIN

    @Prozessstart <= @Prozessstart + 1

    END

    is supposed to be doing.  If it's another IF, then you'll need to nest it.

    BEGIN

    IF@Prozessstart <= @Prozessstart + 1
    BEGIN
    INSERT INTO ....
    END
    END

     


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • thanks a lot , that was helpfull

  • You're welcome.  You could also do:

    Create TRIGGER [dbo].[tinsert]

    ON [dbo].[TBL_LiveData]

    AFTER UPDATE

    As

    DECLARE
    @Prozessstart integer = 0 -- An alternative method of setting a variable.
    ,@liveDataValue integer ;

    SELECT @liveDataValue = value FROM dbo.tbl_liveData tld WHERE tld.ConfigID = 251; ---Another method to set a variable.

    IF @liveDataValue =0

    BEGIN
    SET @Prozessstart = 0 ;

    END

    IF @liveDataValue =1 AND @Prozessstart < 1

    BEGIN

    IF @Prozessstart <= @Prozessstart + 1
    BEGIN
    INSERT INTO ....
    END
    END

    You would only be reading from dbo.tbl_liveData once then.

     


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • the problem is that 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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply