0

I have a table on our database which is used to log user login attempts. The table uses a trigger on the insert to write the current Date/Time to a field called CreatedOn

The script for creating the trigger:

CREATE TRIGGER [dbo].[utr_SecurityLog_Insert] ON [dbo].[SecurityLog] FOR 
INSERT AS Begin Update SecurityLog SET [CreatedOn] = getdate () 
FROM inserted i, SecurityLog a WHERE i.SecurityLogId = a.SecurityLogId End;

The table looks like this:

CREATE TABLE [dbo].[SecurityLog](
    [SecurityLogId] [int] IDENTITY(0,1) NOT NULL,
    [SecurityOfficeId] [int] NULL,
    [SecurityWebId] [int] NULL,
    [IPAddress] [varchar](50) NULL,
    [UserName] [varchar](50) NULL,
    [UserPass] [varchar](50) NULL,
    [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_SecurityLog] PRIMARY KEY CLUSTERED 
(
    [SecurityLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

It's nothing special. I use this trigger code all across the database. Virtually every table has identical CreatedOn and ModifiedOn fields driven by similar trigger scripts.

Now when I attempt an INSERT into the SecurityLog table I am getting the following error:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 523 [code] => 523 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.
[message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. ) )

This database has been in use for twelve years without so much as a hiccup. I understand that somehow the trigger is producing a response which is being classed as output but I have no idea why this is happening in this isolated instance, how to fix it or why this should start happening to one table. Is it possible it is a driver issue?

[EDIT]

I have been able to 'fix' this issue by adding SET NOCOUNT ON and SET NOCOUNT OFFto the SQL like this:

SET NOCOUNT ON;INSERT INTO SecurityLog (IPAddress,SecurityWebId,UserName,UserPass) VALUES (?,?,?,?);SET NO COUNT OFF

Whilst it 'fixes' the issue it still does not explain why this one table should behave like this. It is perplexing me as I want to address the issue rather than paper over the cracks.

Coilover
  • 49
  • 5
  • What purpose does that trigger serve, why not just have a *default* of `getdate()` for the column? – Stu Dec 31 '21 at 12:29
  • I think that whoever setup the database in the first place wanted the flexibility of being able to disable and enable the triggers for DBA tasks and mass updates. Not really sure. It's a valid point but I have inherited what I have. For years this has never presented and issue and has never been top priority for an update. – Coilover Dec 31 '21 at 12:33
  • Why are you using that 1980's JOIN syntax and not the explicit ANSI-92 syntax? Also, the error *is* telling you the problem. What about the error don't you understand, it is *very* specific, and we can try to elaborate. – Thom A Dec 31 '21 at 12:35
  • Does this table have other insert trigger as well? If so, you may want to check all the triggers associated with this table. – sonam81 Dec 31 '21 at 12:35
  • 1
    You don't need to do `SET NOCOUNT OFF;` at the end, it will reset automatically at the end of the trigger. Looks like you could remove the trigger and just use a default value `ALTER TABLE SecurityLog ADD CONSTRAINT DF_CreatedOn DEFAULT GETDATE() FOR CreatedOn;` – Charlieface Dec 31 '21 at 12:40
  • "Why are you using that 1980's JOIN syntax and not the explicit ANSI-92 syntax? Also, the error is telling you the problem. What about the error don't you understand, it is very specific, and we can try to elaborate." What 1980's JOIN syntax? I cannot understand or see what the trigger is returning when the same script works across and entire database with no issues until now. – Coilover Dec 31 '21 at 12:43
  • 2
    Does this answer your question? [A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active](https://stackoverflow.com/questions/27998361/a-trigger-returned-a-resultset-and-or-was-running-with-set-nocount-off-while-ano) The "Rows affected" is treated by PDO as a resultset, so you need `NOCOUNT ON` – Charlieface Dec 31 '21 at 12:44
  • "Does this table have other insert trigger as well? If so, you may want to check all the triggers associated with this table." - No other insert trigger exists. That is what is baffling me. – Coilover Dec 31 '21 at 12:44
  • 1
    `FROM inserted i, SecurityLog a WHERE i.SecurityLogId = a.SecurityLogId` that 1980's syntax. As for the error, why haven't you just tried adding `SET NOCOUNT ON;` inside the trigger, like the error suggests? – Thom A Dec 31 '21 at 12:45
  • The syntax is probably from looking up a create script on the internet. If you have a good reference for ANSI-92 I am happy to update it. I can fix it by setting the NOCOUNT ON but I was more concerned why this was happening to this one table/trigger combination. When one spurious error appears with no easy explanation as to WHY in this one case it is causing an error it does lead to an investigation. That is what I am doing here - investigating – Coilover Dec 31 '21 at 12:50
  • *"with no easy explanation as to WHY"* huh? From said error, with added emphasis: "A trigger returned a resultset and/or **was running with SET NOCOUNT OFF** while another outstanding result set was active." The error tells you **exactly** what the problem is. – Thom A Dec 31 '21 at 12:53
  • See [Bad Habits to Kick : Using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) in regards to the more recent (30 year old) explicit join syntax. – Thom A Dec 31 '21 at 12:54
  • "with no easy explanation as to WHY" huh? From said error, with added emphasis: "A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active." The error tells you exactly what the problem is." OK I get it. I see what you are saying here. I do understand what the problem is and how to fix it. What I fail to understand is why this is happening on this one table on a database with 120 other tables which have been happily working without this error for twelve years. – Coilover Dec 31 '21 at 12:58
  • Because, presumably, those other tables don't have triggers on them, or have explicitly have `NOCOUNT` set to `ON` within their triggers. – Thom A Dec 31 '21 at 12:59
  • Or the code that executes the statements causing the trigger to execute ignore / avoid the error. – SMor Dec 31 '21 at 13:52

0 Answers0