question

marka92 avatar image
marka92 asked

Trigger Not Updating second Table

I have a trigger that should update the date modified that is stored in another table, whenever certain fields are updated in my first table. I also want the second table to update the date in the first table if some fields are updated in its table. I have written this, but, it is updating the table that my trigger is on. I have enabled this update trigger on both tables, but, I am missing something. here is my trigger: ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[HealthCenterAddresses] FOR UPDATE AS declare @infochanged datetime; declare @centername nvarchar(255); declare @address1 varchar(50); declare @phone varchar(10); declare @location geography; declare @status varchar(10); select @infochanged=i.DateModified from inserted i; select @centername=i.CenterName from inserted i; select @address1=i.Address from inserted i; select @phone=i.PhoneNumber from inserted i; select @location=i.SpatialLocation from inserted i; select @status=i.Status from inserted i; if update(CenterName) set @infochanged = GETDATE(); if update(Address) set @infochanged = GETDATE(); if update(PhoneNumber) set @infochanged = GETDATE(); if update(SpatialLocation) set @infochanged = GETDATE(); if update(Status) set @infochanged = GETDATE(); insert into [dbo].[RHDPrograms] (DateModified) values(GETDATE()); PRINT 'AFTER UPDATE Trigger fired.' GO
triggers
16 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Your trigger has a lot of redundant code and is logically equivalent to this: ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[HealthCenterAddresses] FOR UPDATE AS insert into [dbo].[RHDPrograms] (DateModified) values(GETDATE()); This makes me question whether you actually intended to do somthing other than just insert a date into a table???
2 Likes 2 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
So let's see if I've got this right. You've got two tables, `TableA` and `TableB`; each of which has a Trigger, let's say `TriggerA` and `TriggerB`. TriggerA also affects TableB, and TriggerB also affects TableA? What does the other Trigger actually do? I assume (from your question) that it's a trigger on the `RHDPrograms` table.
0 Likes 0 ·
marka92 avatar image marka92 commented ·
The trigger will update the datemodified field in both tables after any column in either table is changed/modified.If table A gets updated the datemodified field gets updated, and vice versa for table B.
0 Likes 0 ·
marka92 avatar image marka92 commented ·
Mister Magoo, when you say"This makes me question whether you actually intended to do somthing other than just insert a date into a table???" You are right , I only want to update the date when a table is changed. Right now, when I update some column in either table, it only updates the datemodified in the table that I edited.
0 Likes 0 ·
marka92 avatar image marka92 commented ·
You suggestion will not add the date to already existing rows. You query adds a new row with only the fields modified, and the new date(GetDate())The reason that I declared all of the fields in my trigger, because I want to fire the trigger whenever any of theses rows change.All of the rows in my table have the same date, and if any row is modified in either table, I want the date to reflect this.
0 Likes 0 ·
Show more comments
Mister Magoo avatar image
Mister Magoo answered
Try this: I have changed the use of the "UPDATE()" function to a specific check for a value change because UPDATE() being true just indicates that the column was named in the update, not that it was actually changed....Also I have left out the SpatialLocation because it doesn't work on my machine. CREATE TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[Table A] FOR UPDATE AS PRINT 'AFTER UPDATE Trigger fired.' -- Check if any of the columns have changed values -- Also check the @@nestlevel to prevent an infinite loop IF EXISTS( SELECT 1 FROM inserted join deleted on inserted.locationid = deleted.locationid WHERE inserted.CenterNamedeleted.CenterName OR inserted.Address deleted.Address OR inserted.PhoneNumber deleted.PhoneNumber OR inserted.Status deleted.Status ) AND @@nestlevel=1 UPDATE [dbo].[Table B] SET DateModified = (GETDATE()); GO CREATE TRIGGER [dbo].[trgAfterProgramUpdate] ON [dbo].[Table B] FOR UPDATE AS PRINT 'AFTER UPDATE Trigger fired.' -- Check if any of the columns have changed values -- Also check the @@nestlevel to prevent an infinite loop IF EXISTS( SELECT 1 FROM inserted join deleted on inserted.locationid = deleted.locationid WHERE inserted.Programdeleted.Program OR inserted.Address deleted.Address OR inserted.PhoneNumber deleted.PhoneNumber OR inserted.Status deleted.Status ) AND @@nestlevel=1 UPDATE [dbo].[Table A] SET DateModified = (GETDATE());
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
What bothers me is WHY? What is the purpose of updating Table B when Table A gets updated, but leaving the date modified on Table A exactly as it was before the update???? The purpose of this baffles me, so if you have time to explain why you want to do this it would be interesting...
0 Likes 0 ·
marka92 avatar image marka92 commented ·
I dont want to do this, I want both dates to change, but I did not have it working yet. I did this, and it is working: I will look at what you,posted and digest the comment you made about the infinite loop.
0 Likes 0 ·
marka92 avatar image marka92 commented ·
I ran this query: UPDATE [dbo].[TableB] SET Status = 'Inactive' WHERE LocationID = 20 GO //The good news is, TableA's datemodified was updated, but TableB's wasnt. I need to make sure that the table datemodified column in the table that is being modified is updated as well.
0 Likes 0 ·
Mister Magoo avatar image Mister Magoo commented ·
I take it you can't include [Table A].[DateModified] in the UPDATE where you set the status? If not, you could just include an update to the base table in the trigger: Trigger for Table A:(do the same for B) CREATE TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[Table A] FOR UPDATE AS PRINT 'AFTER UPDATE Trigger fired.' -- Check if any of the columns have changed values -- Also check the @@nestlevel to prevent an infinite loop IF EXISTS( SELECT 1 FROM inserted join deleted on inserted.locationid = deleted.locationid WHERE inserted.CenterNamedeleted.CenterName OR inserted.Address deleted.Address OR inserted.PhoneNumber deleted.PhoneNumber OR inserted.Status deleted.Status ) AND @@nestlevel=1 BEGIN UPDATE [dbo].[Table A] SET DateModified = (GETDATE()); UPDATE [dbo].[Table B] SET DateModified = (GETDATE()); END
0 Likes 0 ·
marka92 avatar image marka92 commented ·
Yes, that is true, I cannot UPDATE the date where I set the status. I am grateful for your insight. I was way off mark on my original query :-). But you pointed me in the right direction, thank you very much for taking the time to help a newbie. I marked your post as the answer.
0 Likes 0 ·
marka92 avatar image
marka92 answered
Table A CenterName Address Zip Code datemodified Health Partners 123 King st 15478 2013-02-25 00:00:00.000 Table B Program Address Zip Code datemodified MyProgram 458 RoadWay 12458 2013-02-25 00:00:00.000 If any of the data in either column gets changed, the date modified column in BOTH tables gets updated the datemodified column is the same for every row in every table.My trigger is only working on Table A, it is no updating the date in Table B.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

marka92 avatar image
marka92 answered
CREATE TABLE [dbo].[Table A]( [LocationID] [int] IDENTITY(1,1) NOT NULL, [CenterName] [nvarchar](255) NULL, [TypeofCenter] [varchar](50) NULL, [Address] [varchar](50) NULL, [Address2] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [char](2) NULL, [ZipCode] [varchar](10) NULL, [PhoneNumber] [varchar](20) NULL, [Website] [nvarchar](255) NULL, [PatientServices] [varchar](100) NULL, [SpatialLocation] [geography] NULL, [Status] [varchar](10) NULL, [DateModified] [datetime] NULL, CONSTRAINT [PK_HealthMappingAddresses] PRIMARY KEY CLUSTERED ( [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT INTO [dbo].[Table A] ([CenterName] ,[TypeofCenter] ,[Address] ,[City] ,[State] ,[ZipCode] ,[PhoneNumber] ,[Website] ,[PatientServices] ,[SpatialLocation] ,[Status] ,[DateModified]) VALUES ('Some Place' ,'Free' ,'123 Roadway' ,'Anywhere' ,'OK' ,'12458' ,'(254)857-6598' ,' www.somesite.com' ,'Families' ,'0xE6100000010CFCFF7F04ED974440FEFF7F90814C52C0' ,'Active' ,GETDATE()) GO //Table B CREATE TABLE [dbo].[Table B]( [LocationID] [int] IDENTITY(1,1) NOT NULL, [Program] [varchar](100) NULL, [ProcessLevel] [char](10) NULL, [Address] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [char](2) NULL, [ZipCode] [varchar](10) NULL, [PhoneNumber] [varchar](20) NULL, [Website] [nvarchar](255) NULL, [SpatialLocation] [geography] NULL, [Status] [varchar](10) NULL, [DateModified] [datetime] NULL, CONSTRAINT [PK_RHDPrograms] PRIMARY KEY CLUSTERED ( [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT INTO [dbo].[Table B] ([Program] ,[ProcessLevel] ,[Address] ,[City] ,[State] ,[ZipCode] ,[PhoneNumber] ,[Website] ,[SpatialLocation] ,[Status] ,[DateModified]) VALUES ('Some Program' ,'MNHTGR' ,'4587 Some St' ,'myCity' ,'NJ' ,'12598' ,'(658)458-9874' ,' www.awebsite.org' ,'0xE6100000010C020000E0493F3E4000000000587354C0' ,'Activr' ,GETDATE()) GO //Trigger for Table A ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[TableA] FOR UPDATE AS declare @infochanged datetime; declare @centername nvarchar(255); declare @address1 varchar(50); declare @phone varchar(10); declare @location geography; declare @status varchar(10); select @infochanged=i.DateModified from inserted i; select @centername=i.CenterName from inserted i; select @address1=i.Address from inserted i; select @phone=i.PhoneNumber from inserted i; select @location=i.SpatialLocation from inserted i; select @status=i.Status from inserted i; if update(CenterName) set @infochanged = GETDATE(); if update(Address) set @infochanged = GETDATE(); if update(PhoneNumber) set @infochanged = GETDATE(); if update(SpatialLocation) set @infochanged = GETDATE(); if update(Status) set @infochanged = GETDATE(); UPDATE [dbo].[Table B] SET DateModified = (GETDATE()); PRINT 'AFTER UPDATE Trigger fired.' //Trigger for TableB ALTER TRIGGER [dbo].[trgAfterProgramUpdate] ON [dbo].[TableB] FOR UPDATE AS declare @infochanged datetime; declare @programname nvarchar(100); declare @address1 varchar(50); declare @phone varchar(20); declare @location geography; declare @status varchar(10); select @infochanged=i.DateModified from inserted i; select @programname=i.Program from inserted i; select @address1=i.Address from inserted i; select @phone=i.PhoneNumber from inserted i; select @location=i.SpatialLocation from inserted i; select @status=i.Status from inserted i; if update(Program) set @infochanged = GETDATE(); if update(Address) set @infochanged = GETDATE(); if update(PhoneNumber) set @infochanged = GETDATE(); if update(SpatialLocation) set @infochanged = GETDATE(); if update(Status) set @infochanged = GETDATE(); UPDATE TableA SET DateModified =(GETDATE()); PRINT 'AFTER UPDATE Trigger fired.' //an UPDATE statement UPDATE TableA Set Status = 'Inactive'
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

marka92 avatar image marka92 commented ·
Okay, I commented this out of the TableB trigger: --select @programname=i.Program from inserted i; --select @address1=i.Address from inserted i; --select @phone=i.PhoneNumber from inserted i; --select @location=i.SpatialLocation from inserted i; --select @status=i.Status from inserted i; And TableA 's datemodified was updated to todays date, and , no new row was added. But, TableB 's datemodified wasnt updated.I may be missing an update soemwhere.
0 Likes 0 ·
Mister Magoo avatar image Mister Magoo commented ·
As I said before, all those selects into variables are doing absolutely nothing except wasting resources... The same is true for the "if update(...)" statements - absolutely doing nothing....
0 Likes 0 ·
marka92 avatar image marka92 commented ·
Hey Mr. Magoo, The triggers work when I run the queries form SQL.But, when I run the stored proceudre that perfomrs the updates from within my application, the triggers dont fire. Any suggestions?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.