February 10, 2016 at 3:55 pm
Hello comunity
I have build this trigger :
[Code="sql"]
CREATE TRIGGER [dbo].[trgAfterINSERT] ON [dbo].[fi]
FOR INSERT
AS
SET NOCOUNT ON;
DECLARE @fistamp VARCHAR(25);
DECLARE @lote VARCHAR (30);
SELECT @fistamp =i.fistamp, @lote = ISNULL(RTRIM(i.lobs2),'') from inserted i;
-- perform update here in SL table
BEGIN
PRINT @lote
UPDATE sl SET u_loteori = @lote
FROM SL where sl.fistamp = @fistamp
AND sl.slstamp = @fistamp
PRINT 'AFTER INSERT Trigger fired'
PRINT @lote
END
[/code]
I don´t know why my field "u_loteori" are not update with the value "LOT - 999",because if i delete the row inserted on my table FI and i execute the insert statment on my table FI the PRINT @LOTE show me om QA the value : LOT - 999
Could someone give me a solution !?
Best regards
Luis
February 10, 2016 at 4:52 pm
Hello comunity
I solve the problem that is due to have another INSERT TRIGGER, then i run after rebuild my Trigger the triggers - order of execution
exec sp_settriggerorder @triggername = 'trgAfterINSERT', @order = 'last', @stmttype = 'INSERT'
Many thanks
Luis
February 10, 2016 at 5:03 pm
You should never write a trigger assuming only a single row gets inserted.
You cannot control which queries will be executed against the table, so make sure your triggers are done properly.
CREATE TRIGGER [dbo].[trgAfterINSERT] ON [dbo].[fi]
FOR INSERT
AS
SET NOCOUNT ON;
/* this part for test environment only
--check if any records from inserted match SL
SELECT *
FROM SL sl
RIGHT JOIN inserted i ON i.fistamp = sl.fistamp AND i.fistamp = sl.slstamp
*/
-- perform update here in SL table
UPDATE sl
SET u_loteori = ISNULL(RTRIM(i.lobs2),'')
FROM SL sl
INNER JOIN inserted i ON i.fistamp = sl.fistamp AND i.fistamp = sl.slstamp
GO
Not to mention - the code is significantly shorter. 🙂
_____________
Code for TallyGenerator
February 10, 2016 at 5:24 pm
I had a client GO OUT OF BUSINESS because all of their triggers were built with the variable problem. And I TOLD them it was going to happen before it did and they didn't listen. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 11, 2016 at 4:39 am
Hello Sergiy
I rebuild my trigger with your script, but if i make a new document and save my column don´t update with the information. Why ??
Also you refer that my script suppose that i have only one row, this is because i use variable ??
Thanks for your help.
Best regards
luis
February 11, 2016 at 3:19 pm
luissantos (2/11/2016)
Hello SergiyI rebuild my trigger with your script, but if i make a new document and save my column don´t update with the information. Why ??
You need to post the actual script of "save my column" query.
Also you refer that my script suppose that i have only one row, this is because i use variable ??
Yes.
_____________
Code for TallyGenerator
February 12, 2016 at 3:34 am
Hello Sergiy
After some test, i change the script like this:
USE [NMYDATABASE]
GO
/****** Object: Trigger [dbo].[trgAfterINSERT] Script Date: 12/02/2016 10:19:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgAfterINSERT] ON [dbo].[fi]
AFTER INSERT
AS
SET NOCOUNT ON;
/* this part for test environment only
--check if any records from inserted match SL
SELECT *
FROM SL sl
RIGHT JOIN inserted i ON i.fistamp = sl.fistamp AND i.fistamp = sl.slstamp
*/
-- perform update here in SL table
UPDATE sl
SET lote = ISNULL(RTRIM(i.lobs2),'')
FROM SL sll
INNER JOIN inserted i ON i.fistamp = sll.fistamp AND i.fistamp = sll.slstamp
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trgAfterINSERT]', @order=N'Last', @stmttype=N'INSERT'
I think on your original trigger script, the alias that you have mencioned for the table SL had the same name of the table , than i changed to sll.
Also, i write the settriggerorder to fire LAST, because the program manufacturer have by default a trigger for Insert.
Now, everything work OK.
Sorry to answer you just now, but I thought it best to first make some tests to rule out the problem.
I will also thanks for the explanation about the question of using the variables.
Just a curiousity about your script, if i uncomment :
--check if any records from inserted match SL
SELECT *
FROM SL sl
RIGHT JOIN inserted i ON i.fistamp = sl.fistamp AND i.fistamp = sl.slstamp
sql server return this error when i hit F5 on QA:
Msg 311, Level 16, State 1, Procedure trgAfterINSERT, Line 29
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
How avoid this error, because this tecnique could be useful.
Many thanks for your great help.
Best regards,
Luis
February 15, 2016 at 2:38 pm
luissantos (2/12/2016)
Just a curiousity about your script, if i uncomment :
--check if any records from inserted match SL
SELECT *
FROM SL sl
RIGHT JOIN inserted i ON i.fistamp = sl.fistamp AND i.fistamp = sl.slstamp
sql server return this error when i hit F5 on QA:
Msg 311, Level 16, State 1, Procedure trgAfterINSERT, Line 29
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
How avoid this error, because this tecnique could be useful.
Many thanks for your great help.
Best regards,
Luis
The table SL must contain a BLOB colum.
Expand "*" into the actual list of columns and remove text/image one from the list.
Actually, leave only those ones which you need to see.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply