I have the following Trigger in Microsoft SQL Server which only receives inserted parameters and inserts them into another table. The trigger works without any problem:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AfterAccess] ON [dbo].[CardAccessHistory] AFTER INSERT
AS
BEGIN
DECLARE @card_id INT = (SELECT Cardholder_ID FROM inserted)
DECLARE @datetime_access DATETIME = (SELECT Trans_Time FROM inserted)
DECLARE @type_access VARCHAR(255) = (SELECT ACR_Name FROM inserted)
DECLARE @payroll_id VARCHAR(20) = (SELECT EmployeeNumber FROM CardHolders.dbo.Cardholders WHERE CardHolder_ID = @card_id)
DECLARE @tag VARCHAR(20) = (SELECT Card_Number FROM inserted)
INSERT INTO table_test (payroll_id, tag, type_access, card_id) VALUES (@payroll_id, @tag, @type_access, @card_id
END
The problem comes when adding conditions to the trigger: All the variables that are commented are already tested and if they work , Comment the variables because the query has more content and to make it more understandable the content of the trigger is manually tested and it works. I mean that I simulate data inserted as variables and the trigger query works and meets the conditions perfectly, the problem comes when activating the trigger does nothing. Any idea what could be happening?
GO
/****** Object: Trigger [dbo].[AfterAccess] Script Date: 03/11/2021 11:51:58 a. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AfterAccess] ON [dbo].[CardAccessHistory] AFTER INSERT
AS
BEGIN
DECLARE @card_id INT = (SELECT Cardholder_ID FROM inserted)
DECLARE @datetime_access DATETIME = (SELECT Trans_Time FROM inserted)
DECLARE @type_access VARCHAR(255) = (SELECT ACR_Name FROM inserted)
DECLARE @payroll_id VARCHAR(20) = (SELECT EmployeeNumber FROM CardHolders.dbo.Cardholders WHERE CardHolder_ID = @card_id)
DECLARE @tag VARCHAR(20) = (SELECT Card_Number FROM inserted)
IF @type_access LIKE '%Salida%'
BEGIN
DECLARE @total_time TIME = (SELECT total_time FROM Attendance WHERE payroll_id = @payroll_id AND CAST(date_attendance AS DATE) = CONVERT(DATE, GETDATE()))
DECLARE @last_type_access_checked VARCHAR(255) = (SELECT TOP (1) ACR_Name FROM CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = CONVERT(DATE, GETDATE()) ORDER BY Trans_Time DESC)
DECLARE @today_date DATE = (SELECT CAST( GETDATE() AS Date ))
IF @last_type_access_checked LIKE '%Entrada%'
BEGIN
DECLARE @last_datetime_access_checked DATETIME = (SELECT TOP(1) Trans_Time FROM CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = CONVERT(DATE, GETDATE()) ORDER BY Trans_Time DESC)
DECLARE @add_minutes INT = (SELECT DATEDIFF(MINUTE, (@last_datetime_access_checked), (@datetime_access)))
IF @total_time IS NULL
BEGIN
DECLARE @minutes_in_time_with_reset_total_time TIME = (DATEADD(MINUTE, (@add_minutes), ('00:00')))
INSERT INTO Attendance (payroll_id, tag, date_attendance, total_time) VALUES (
@payroll_id, @tag, @today_date, @minutes_in_time_with_reset_total_time
)
END
ELSE
BEGIN
DECLARE @minutes_in_time TIME = (DATEADD(MINUTE, (@add_minutes), (@total_time)))
UPDATE Attendance SET total_time = @minutes_in_time WHERE payroll_id = @payroll_id AND CAST(date_attendance AS DATE) = CONVERT(DATE, GETDATE())
END
END
ELSE IF @last_type_access_checked IS NULL
BEGIN
DECLARE @yestertday_last_type_access_checked VARCHAR(255) = (SELECT TOP (1) ACR_Name FROM CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = DATEADD(DAY, -1, GETDATE()) ORDER BY Trans_Time DESC)
IF @yestertday_last_type_access_checked LIKE '%Entrada%'
BEGIN
DECLARE @yesterday_last_datetime_access_checked DATETIME = (SELECT TOP(1) Trans_Time FROM CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = DATEADD(DAY, -1, GETDATE()) ORDER BY Trans_Time DESC)
DECLARE @time_beetween_yesterday_and_today TIME = (SELECT CAST((@datetime_access - @yesterday_last_datetime_access_checked) AS TIME(0)))
DECLARE @time_to_int INT = (SELECT DATEDIFF(MINUTE, 0, @time_beetween_yesterday_and_today))
DECLARE @total_minutes TIME = (DATEADD(MINUTE, (@time_to_int), ('00:00')))
INSERT INTO Attendance (payroll_id, tag, date_attendance, total_time) VALUES (
@payroll_id, @tag, @today_date, @total_minutes
)
END
END
END
END
If I simulate the data of the table if it is functional and it inserts in the other table, that is why I deduce that it is something in the trigger
DECLARE @CardAccessHistory TABLE (
Cardholder_ID INT,
Card_Number VARCHAR(50),
ACR_Name VARCHAR(60),
Trans_Time DATETIME
)
INSERT INTO @CardAccessHistory (Cardholder_ID, Card_Number, ACR_Name, Trans_Time) VALUES (372,'806768214','Entrada Torniquete 1','2021-11-03 11:10:55.000')
DECLARE @card_id INT = 372 --(SELECT Cardholder_ID FROM inserted)
DECLARE @datetime_access DATETIME = '2021-11-02 11:20:55.000' --(SELECT Trans_Time FROM inserted)
DECLARE @type_access VARCHAR(255) = 'Salida Torniquete 5' --(SELECT ACR_Name FROM inserted)
DECLARE @payroll_id VARCHAR(20) = '1234' --(SELECT EmployeeNumber FROM CardHolders.dbo.Cardholders WHERE CardHolder_ID = @card_id)
DECLARE @tag VARCHAR(20) = '4321' --(SELECT Card_Number FROM inserted)
IF @type_access LIKE '%Salida%'
BEGIN
DECLARE @total_time TIME = (SELECT total_time FROM Attendance WHERE payroll_id = @payroll_id AND CAST(date_attendance AS DATE) = CONVERT(DATE, GETDATE()))
DECLARE @last_type_access_checked VARCHAR(255) = (SELECT TOP (1) ACR_Name FROM @CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = CONVERT(DATE, GETDATE()) ORDER BY Trans_Time DESC)
DECLARE @today_date DATE = (SELECT CAST( GETDATE() AS Date ))
PRINT @last_type_access_checked
IF @last_type_access_checked LIKE '%Entrada%'
BEGIN
DECLARE @last_datetime_access_checked DATETIME = (SELECT TOP(1) Trans_Time FROM @CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = CONVERT(DATE, GETDATE()) ORDER BY Trans_Time DESC)
DECLARE @add_minutes INT = (SELECT DATEDIFF(MINUTE, (@last_datetime_access_checked), (@datetime_access)))
IF @total_time IS NULL
BEGIN
DECLARE @minutes_in_time_with_reset_total_time TIME = (DATEADD(MINUTE, (@add_minutes), ('00:00')))
INSERT INTO Attendance (payroll_id, tag, date_attendance, total_time) VALUES (
@payroll_id, @tag, @today_date, @minutes_in_time_with_reset_total_time
)
END
ELSE
BEGIN
DECLARE @minutes_in_time TIME = (DATEADD(MINUTE, (@add_minutes), (@total_time)))
UPDATE Attendance SET total_time = @minutes_in_time WHERE payroll_id = @payroll_id AND CAST(date_attendance AS DATE) = CONVERT(DATE, GETDATE())
END
END
ELSE IF @last_type_access_checked IS NULL
BEGIN
DECLARE @yestertday_last_type_access_checked VARCHAR(255) = (SELECT TOP (1) ACR_Name FROM @CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = DATEADD(DAY, -1, GETDATE()) ORDER BY Trans_Time DESC)
IF @yestertday_last_type_access_checked LIKE '%Entrada%'
BEGIN
DECLARE @yesterday_last_datetime_access_checked DATETIME = (SELECT TOP(1) Trans_Time FROM @CardAccessHistory WHERE Cardholder_ID = @card_id AND CAST(Trans_Time AS DATE) = DATEADD(DAY, -1, GETDATE()) ORDER BY Trans_Time DESC)
DECLARE @time_beetween_yesterday_and_today TIME = (SELECT CAST((@datetime_access - @yesterday_last_datetime_access_checked) AS TIME(0)))
DECLARE @time_to_int INT = (SELECT DATEDIFF(MINUTE, 0, @time_beetween_yesterday_and_today))
DECLARE @total_minutes TIME = (DATEADD(MINUTE, (@time_to_int), ('00:00')))
INSERT INTO Attendance (payroll_id, tag, date_attendance, total_time) VALUES (
@payroll_id, @tag, @today_date, @total_minutes
)
END
END
END
How to debug a trigger? Sql Management Studio 17.9 or lower required, because in version 18.0 and higher, this possibility has been removed.
Click on the Debug icon . When the line turns yellow, press F11
And you can display the local variables window at the bottom, which will give you step-by-step information about its content.
Another Option If you don't have the tool, is to plague the message trigger to collect information in the output console.
Then you execute an insert statement and see where it went.