Question on Trigger
-
I have a trigger that will insert the inserted column(s) into another table.
create table a (ID int, A int, B int)
create table b (ID int, A int, B int, DateTimeChanged datetime default getdate())
create trigger TR_ABC on a for update as
if update (a)
insert b (A) select A from inserted
if update (b)
insert b (B) select B from inserted
go
insert a values (1, 100, 200)
insert a values (2, 1000, 2000)
update a set A = 101 where id = 1
update a set B = 201 where id = 1
update a set A = 1001, B = 2001 where id = 2
This trigger works well here when I updated table a. However in my real tables in production, when I updated both column A and column B in a same statement, sometime the triger only insert the inserted value in column A in table B; the inserted for column B was not inserted into table b. Do you know why?
Thanks
-
Recommend you post the actual trigger code and a bit of information on the schema of the tables...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here they are:
CREATE TABLE [Bugs] (
[BugID] [int] IDENTITY (1, 1) NOT NULL ,
[ProjectID] [int] NOT NULL ,
[CreatorID] [int] NOT NULL ,
[ReportedByID] [int] NOT NULL ,
[AssignedToID] [int] NOT NULL ,
[PriorityTypeID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[SeverityTypeID] [int] NOT NULL ,
[Name] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BuildNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EstimatedDuration] [real] NOT NULL ,
[DurationUnitTypeID] [int] NOT NULL ,
[ActualDuration] [real] NOT NULL ,
[ActualUnitTypeID] [int] NOT NULL ,
[DateFound] [datetime] NOT NULL ,
[DateFixed] [datetime] NOT NULL ,
[BuildNumberOfFix] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReplicationProcedures] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Resolution] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [timestamp] NOT NULL ,
[WorkflowStepID] [int] NOT NULL ,
[PercentComplete] [tinyint] NOT NULL ,
[LastUpdatedByID] [int] NOT NULL ,
[LastUpdatedDateTime] [datetime] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[PubliclyViewable] [bit] NOT NULL ,
[ReportedByCustomerContactID] [int] NOT NULL ,
[Archived] [bit] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [Bugs_Changes] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AuditDateTime] [datetime] NULL CONSTRAINT [DF__Bugs_C__Audit__7A8729A3] DEFAULT (getdate()),
[BugID] [int] NULL ,
[ProjectID] [int] NULL ,
[OldStatusTypeID] [int] NULL ,
[NewStatusTypeID] [int] NULL ,
[OldWorkflowStepID] [int] NULL ,
[NewWorkflowStepID] [int] NULL ,
[OldAssignedToID] [int] NULL ,
[NewAssignedToID] [int] NULL ,
[OldLastUpdatedByID] [int] NULL ,
[NewLastUpdatedByID] [int] NULL ,
[DateFound] [datetime] NULL ,
[DateFixed] [datetime] NULL ,
[LastUpdatedDateTime] [datetime] NULL ,
[CreatedDateTime] [datetime] NULL )
GO
CREATE trigger TRG_Update on Bugs for update
as
if update(StatusTypeID)
insert Bugs_Changes (BugID, ProjectID, OldStatusTypeID, NewStatusTypeID, DateFound, DateFixed, CreatedDateTime)
select BugID, ProjectID, (select StatusTypeID from deleted), StatusTypeID, DateFound, DateFixed, CreatedDateTime from inserted
if update(WorkflowStepID)
insert Bugs_Changes (BugID, ProjectID, OldWorkflowStepID, NewWorkflowStepID, DateFound, DateFixed, CreatedDateTime)
select BugID, ProjectID, (select WorkflowStepID from deleted), WorkflowStepID, DateFound, DateFixed, CreatedDateTime from inserted
if update(AssignedToID)
insert Bugs_Changes (BugID, ProjectID, OldAssignedToID, NewAssignedToID, DateFound, DateFixed, CreatedDateTime)
select BugID, ProjectID, (select AssignedToID from deleted), AssignedToID, DateFound, DateFixed, CreatedDateTime from inserted
if update(LastUpdatedByID)
insert Bugs_Changes (BugID, ProjectID, OldLastUpdatedByID, NewLastUpdatedByID, DateFound, DateFixed, CreatedDateTime)
select BugID, ProjectID, (select LastUpdatedByID from deleted), LastUpdatedByID, DateFound, DateFixed, CreatedDateTime from inserted
GO
When both columns StatusTypeID and WorkflowStepId in table Bugs are updated, only NewStatusTypeID and OldStatusTypeID in table Bugs_Changes are inserted. OldWorkflowStepID and NewWorkflowStepID should be inserted also.
Thanks.
-
You are missing joins between the PK of the INSERTed and DELETEd tables in all such SELECT's in the code you posted...
Recommend you change them all similar to the following...
INSERT Bugs_Changes
(BugID, ProjectID, OldStatusTypeID, NewStatusTypeID, DateFound, DateFixed, CreatedDateTime)
SELECT i.BugID,
i.ProjectID,
d.StatusTypeID AS OldStatusTypeID,
i.StatusTypeID,
i.DateFound,
i.DateFixed,
i.CreatedDateTime
FROM INSERTED i,
DELETED d
WHERE i.BugID = d.BugID
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
but the trigger works fine:
insert
Bugs (ProjectID, CreatorID, AssignedToID, StatusTypeID, WorkflowStepID) values (100, 1, 15, 1, 1)
insert
Bugs (ProjectID, CreatorID, AssignedToID, StatusTypeID, WorkflowStepID) values (101, 1, 25, 1, 1)
update
Bugs set StatusTypeID = 7, WorkflowStepID = 199 where BugID = 1
update
Bugs set StatusTypeID = 1, WorkflowStepID = 100 where BugID = 1
update
Bugs set StatusTypeID = 6, WorkflowStepID = 500, AssignedToID = 16 where BugID = 2
select
* from bugs
select
* from bugs_changes
The trigger recorded all changes as expected.
-
Yes... it works fine... if you are only updating one row at a time... try updating more than one row at a time and see what happens... BOOOMM!
I'm looking at the other stuff but thought I'd point out what's gonna happen with your trigger code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You are correct
-
I am unable to duplicate the failure you say you are experiencing... that's not to say it's not happening... I just can't duplicate it because I don't have your data.
My recommendation is fix the triggers, as I suggested, and see if the situation corrects itself. Think "low hanging fruit" and "peel one potato at a time"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply