December 15, 2016 at 1:42 pm
I have created a trigger on a table but its not working when a user tries to update the table. Syntax is below. What am I missing and why the trigger isn't working?
CREATE TRIGGER Safety_tiudPatient
ON [Safety].[Patient]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF @@ROWCOUNT = 0 --no rows affected, no reason for trigger to fire
BEGIN
RETURN
END
ELSE
IF @@ROWCOUNT > 1 --multiple rows affected, trigger can not handle at this time
BEGIN
RETURN
END
ELSE
IF @@ROWCOUNT = 1
BEGIN
SET NOCOUNT ON
DECLARE @auditLinkGUID AS UNIQUEIDENTIFIER;
DECLARE @modifiedData AS TINYINT;
DECLARE @systemUserID AS UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000'
DECLARE @schemaName AS NVARCHAR(500) = 'Safety'
DECLARE @tableRoot AS NVARCHAR(500) = 'Patient'
DECLARE @tableName AS NVARCHAR(500)
SET @tableName = @schemaName + '.' + @tableRoot
SET @auditLinkGUID = newid();
SET @modifiedData = 0; --no
DECLARE @action char(1);
SET @action = (CASE WHEN EXISTS(SELECT * FROM inserted)
AND EXISTS(SELECT * FROM deleted)
THEN 'U' -- Set Action to UPDATE.
WHEN EXISTS(SELECT * FROM inserted )
THEN 'I' -- Set Action to INSERT.
WHEN EXISTS(SELECT * FROM deleted)
THEN 'D' -- Set Action to DELETE.
ELSE NULL -- Skip. It may have been a "failed delete".
END)
IF @action IN ('I', 'U')
BEGIN
IF @action = 'I'
BEGIN
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT i.PatientID,
i.FacilityID,
i.FacilityPatientID,
i.PatientFirstName,
i.PatientMiddleInitial,
i.PatientLastName,
i.PatientPseudonym,
i.GenderID,
i.TransactionAuditGUID,
i.InitDate,
i.InitUserID,
i.LastUpdateDate,
i.LastUpdateUserID,
i.Status,
'I',
SYSDATETIME(),
i.LastUpdateDate,
i.LastUpdateUserID,
0,
@auditLinkGUID
FROM inserted i
)
END -- if 'I'
ELSE
IF @Action = 'U'
BEGIN
SELECT @modifiedData = CASE WHEN ISNULL(i.Status, '0') != ISNULL(d.status, '0') THEN 2
WHEN ISNULL(i.FacilityID, 0) != ISNULL(d.FacilityID, 0) THEN 1
WHEN ISNULL(i.FacilityPatientID, '0') != ISNULL(d.FacilityPatientID, '0') THEN 1
WHEN ISNULL(i.PatientFirstName, '0') != ISNULL(d.PatientFirstName, '0') THEN 1
WHEN ISNULL(i.PatientMiddleInitial, '0') != ISNULL(d.PatientMiddleInitial, '0') THEN 1
WHEN ISNULL(i.PatientLastName, '0') != ISNULL(d.PatientLastName, '0') THEN 1
WHEN ISNULL(i.PatientPseudonym, '0') != ISNULL(d.PatientPseudonym, '0') THEN 1
WHEN ISNULL(i.GenderID, 0) != ISNULL(d.GenderID, 0) THEN 1
ELSE 0 END
FROM inserted i
INNER JOIN deleted d
ON i.PatientID = d.PatientID
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT d.PatientID,
d.FacilityID,
d.FacilityPatientID,
d.PatientFirstName,
d.PatientMiddleInitial,
d.PatientLastName,
d.PatientPseudonym,
d.GenderID,
d.TransactionAuditGUID,
d.InitDate,
d.InitUserID,
d.LastUpdateDate,
d.LastUpdateUserID,
d.Status,
'UB',
SYSDATETIME(),
i.LastUpdateDate,
i.LastUpdateUserID,
@modifiedData,
@auditLinkGUID
FROM inserted i
INNER JOIN deleted d
ON i.PatientID = d.PatientID
)
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT i.PatientID,
i.FacilityID,
i.FacilityPatientID,
i.PatientFirstName,
i.PatientMiddleInitial,
i.PatientLastName,
i.PatientPseudonym,
i.GenderID,
i.TransactionAuditGUID,
i.InitDate,
i.InitUserID,
i.LastUpdateDate,
i.LastUpdateUserID,
i.Status,
'UA',
SYSDATETIME(),
i.LastUpdateDate,
i.LastUpdateUserID,
@modifiedData,
@auditLinkGUID
FROM inserted i
)
END -- IF 'U'
END --if 'I' or 'U'
ELSE
IF @Action = 'D'
BEGIN
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT d.PatientID,
d.FacilityID,
d.FacilityPatientID,
d.PatientFirstName,
d.PatientMiddleInitial,
d.PatientLastName,
d.PatientPseudonym,
d.GenderID,
d.TransactionAuditGUID,
d.InitDate,
d.InitUserID,
d.LastUpdateDate,
d.LastUpdateUserID,
d.Status,
'D',
SYSDATETIME(),
SYSDATETIME(),
@systemUserID,
0,
@auditLinkGUID
FROM deleted d
)
END -- If 'D'
END -- if @@Rowcount = 1
END --final END
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 15, 2016 at 2:46 pm
I'm not 100% sure, but I don't think @@ROWCOUNT is going to have a non-zero value at the point that you test it, which is right at the beginning. I'm under the impression that a trigger has it's own set of values for things like @@ROWCOUNT, and that it doesn't inherit that value from whatever query results in the trigger firing. However, I could be completely wrong about that, and perfectly happy to be corrected. Also, you have to enable the trigger, so if you haven't done that yet, it might be the solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 15, 2016 at 3:13 pm
It looks like the initial if statements are breaking your @@ROWCOUNT = 1 check 🙂
CREATE TABLE TEST_TRIG_ONE(COL_ONE varchar(30), COL_TWO varchar(30), COL_THREE varchar(30))
CREATE TABLE TEST_TWO(COL_ONE varchar(30), COL_TWO varchar(30), COL_THREE varchar(30))
DROP TRIGGER TestyTrigger;
GO
CREATE TRIGGER TestyTrigger ON TEST_TRIG_ONE AFTER UPDATE AS
BEGIN
IF @@ROWCOUNT = 20 BEGIN RETURN END
INSERT INTO TEST_TWO(COL_ONE, COL_TWO, COL_THREE) SELECT COL_ONE, CASE WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'UPDATE' ELSE 'NOT' END,CAST(@@ROWCOUNT AS varchar) FROM inserted
END
INSERT INTO TEST_TRIG_ONE(COL_ONE) VALUES('Hello')
UPDATE TEST_TRIG_ONE SET COL_ONE = 'Bye'
SELECT * FROM TEST_TWO
Instead of having all that logic why not just do something simpler like this and drop all the extra logic at the beginning?
IF (SELECT COUNT(*) FROM inserted) = 1 AND (SELECT COUNT(*) FROM deleted) = 0 BEGIN
<do your insert code>
END
IF (SELECT COUNT(*) FROM inserted) = 1 AND (SELECT COUNT(*) FROM deleted) = 1 BEGIN
<do your update code>
END
IF (SELECT COUNT(*) FROM inserted) = 0 AND (SELECT COUNT(*) FROM deleted) = 1 BEGIN
<do your delete code>
END
January 4, 2017 at 1:08 pm
This did work. However, I have a question. Can someone explain why the @@ROWCOUNT = 0 statement was causing the problem with the trigger. I'm assuming since I have two ELSE statements, that it would continue to drop down into the next section until it finds the one that is TRUE. Does the code not drop down into the next ELSE part of that section of code? Am I missing some syntax? I'm assuming if had one too many/few BEGIN or END statements that it would error out. So I'm a little confused and want to understand why it is not working.
USE [SafetyAnalytics]
GO
/****** Object: Trigger [Safety].[Safety_tiudPatient] Script Date: 1/4/2017 2:06:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Safety].[Safety_tiudPatient]
ON [Safety].[Patient]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
--IF @@ROWCOUNT = 0 --no rows affected, no reason for trigger to fire
--BEGIN
-- RETURN
--END
--ELSE
-- IF @@ROWCOUNT > 1 --multiple rows affected, trigger can not handle at this time
-- BEGIN
-- RETURN
-- END
--ELSE
IF @@ROWCOUNT = 1
BEGIN
SET NOCOUNT ON
DECLARE @auditLinkGUID AS UNIQUEIDENTIFIER;
DECLARE @modifiedData AS TINYINT;
DECLARE @systemUserID AS UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000'
DECLARE @schemaName AS NVARCHAR(500) = 'Safety'
DECLARE @tableRoot AS NVARCHAR(500) = 'Patient'
DECLARE @tableName AS NVARCHAR(500)
SET @tableName = @schemaName + '.' + @tableRoot
SET @auditLinkGUID = newid();
SET @modifiedData = 0; --no
DECLARE @action char(1);
SET @action = (CASE WHEN EXISTS(SELECT * FROM inserted)
AND EXISTS(SELECT * FROM deleted)
THEN 'U' -- Set Action to UPDATE.
WHEN EXISTS(SELECT * FROM inserted )
THEN 'I' -- Set Action to INSERT.
WHEN EXISTS(SELECT * FROM deleted)
THEN 'D' -- Set Action to DELETE.
ELSE NULL -- Skip. It may have been a "failed delete".
END)
IF @action IN ('I', 'U')
BEGIN
IF @action = 'I'
BEGIN
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT i.PatientID,
i.FacilityID,
i.FacilityPatientID,
i.PatientFirstName,
i.PatientMiddleInitial,
i.PatientLastName,
i.PatientPseudonym,
i.GenderID,
i.TransactionAuditGUID,
i.InitDate,
i.InitUserID,
i.LastUpdateDate,
i.LastUpdateUserID,
i.Status,
'I',
SYSDATETIME(),
i.LastUpdateDate,
i.LastUpdateUserID,
0,
@auditLinkGUID
FROM inserted i
)
END -- if 'I'
ELSE
IF @Action = 'U'
BEGIN
SELECT @modifiedData = CASE WHEN ISNULL(i.Status, '0') != ISNULL(d.status, '0') THEN 2
WHEN ISNULL(i.FacilityID, 0) != ISNULL(d.FacilityID, 0) THEN 1
WHEN ISNULL(i.FacilityPatientID, '0') != ISNULL(d.FacilityPatientID, '0') THEN 1
WHEN ISNULL(i.PatientFirstName, '0') != ISNULL(d.PatientFirstName, '0') THEN 1
WHEN ISNULL(i.PatientMiddleInitial, '0') != ISNULL(d.PatientMiddleInitial, '0') THEN 1
WHEN ISNULL(i.PatientLastName, '0') != ISNULL(d.PatientLastName, '0') THEN 1
WHEN ISNULL(i.PatientPseudonym, '0') != ISNULL(d.PatientPseudonym, '0') THEN 1
WHEN ISNULL(i.GenderID, 0) != ISNULL(d.GenderID, 0) THEN 1
ELSE 0 END
FROM inserted i
INNER JOIN deleted d
ON i.PatientID = d.PatientID
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT d.PatientID,
d.FacilityID,
d.FacilityPatientID,
d.PatientFirstName,
d.PatientMiddleInitial,
d.PatientLastName,
d.PatientPseudonym,
d.GenderID,
d.TransactionAuditGUID,
d.InitDate,
d.InitUserID,
d.LastUpdateDate,
d.LastUpdateUserID,
d.Status,
'UB',
SYSDATETIME(),
i.LastUpdateDate,
i.LastUpdateUserID,
@modifiedData,
@auditLinkGUID
FROM inserted i
INNER JOIN deleted d
ON i.PatientID = d.PatientID
)
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT i.PatientID,
i.FacilityID,
i.FacilityPatientID,
i.PatientFirstName,
i.PatientMiddleInitial,
i.PatientLastName,
i.PatientPseudonym,
i.GenderID,
i.TransactionAuditGUID,
i.InitDate,
i.InitUserID,
i.LastUpdateDate,
i.LastUpdateUserID,
i.Status,
'UA',
SYSDATETIME(),
i.LastUpdateDate,
i.LastUpdateUserID,
@modifiedData,
@auditLinkGUID
FROM inserted i
)
END -- IF 'U'
END --if 'I' or 'U'
ELSE
IF @Action = 'D'
BEGIN
INSERT INTO Audit.AuditPatient
(
PatientID,
FacilityID,
FacilityPatientID,
PatientFirstName,
PatientMiddleInitial,
PatientLastName,
PatientPseudonym,
GenderID,
TransactionAuditGUID,
InitDate,
InitUserID,
LastUpdateDate,
LastUpdateUserID,
Status,
ChangeTypeID,
ChangeTimestamp,
ChangeDate,
ChangeUserID,
ChangeDataStatus,
AuditLinkGUID
)
(
SELECT d.PatientID,
d.FacilityID,
d.FacilityPatientID,
d.PatientFirstName,
d.PatientMiddleInitial,
d.PatientLastName,
d.PatientPseudonym,
d.GenderID,
d.TransactionAuditGUID,
d.InitDate,
d.InitUserID,
d.LastUpdateDate,
d.LastUpdateUserID,
d.Status,
'D',
SYSDATETIME(),
SYSDATETIME(),
@systemUserID,
0,
@auditLinkGUID
FROM deleted d
)
END -- If 'D'
END -- if @@Rowcount = 1
END --final END
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 4, 2017 at 1:21 pm
New Born DBA (1/4/2017)
Can someone explain why the @@ROWCOUNT = 0 statement was causing the problem with the trigger.
it's not that intuitive, but you have to remember that EVERY command resets @@ROWCOUNT.
BEGIN and END for example, SET ANSI parameters, SET @Variable = , DECLARE @Variable, etc.
Every command.
you were assuming that it was coming from the last insert/update delete, and that is only true if you explicitly grabbed the value as the next statement.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply