October 13, 2021 at 12:43 pm
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 ?
October 13, 2021 at 1:17 pm
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
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 13, 2021 at 1:24 pm
thanks a lot , that was helpfull
October 13, 2021 at 1:37 pm
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 14, 2021 at 11:24 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply