July 9, 2009 at 1:53 pm
I have a trigger on a table that checks for an updated timestamp and if it exists it write that record into another database for reporting. However, this trigger recently has started locking the table so that the updates can not occur in the application. I have to turn the trigger off in order for the application to work. Any recommendations would be helpful.
USE [ProjectServerQA]
GO
/****** Object: Trigger [dbo].[UPDATE_PS_USAGE_TRG] Script Date: 07/09/2009 15:20:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UPDATE_PS_USAGE_TRG] ON [dbo].[MSP_WEB_RESOURCES]
AFTER UPDATE
AS
DECLARE @insertcount int
SELECT @insertcount = COUNT(*) FROM INSERTED
IF @insertcount > 0
BEGIN
insert into ealogs.dbo.application_hit_info(app_name, time_stamp, session_id, personid, remote_user, logon_user, path_info, activity)
select distinct 'ProjectServer', getdate(), INSERTED.wres_nt_account, CORPORATEDIRECTORY.dbo.PERSON.PERSONID,INSERTED.wres_email, INSERTED.res_name, INSERTED.wres_last_connect, 'Sync'
from INSERTED (NOLOCK), msp_web_resources m with (NOLOCK), CORPORATEDIRECTORY.dbo.PERSON (NOLOCK)
where corporatedirectory.dbo.person.email = INSERTED.wres_email collate SQL_Latin1_General_CP1_CI_AS
and m.res_name not like '%Deleted%'
and m.wres_email != 'NULL'
and m.wres_email != ''
END
IF @@ERROR != 0
ROLLBACK TRAN
July 9, 2009 at 6:03 pm
My first thought is you have something blocking this process, while this is being held up check to see if there are any proocesses being blocked or blocking this. I'm willing to bet there is..
CEWII
July 10, 2009 at 7:01 am
I was watching it in my activity monitor but I did not see something trying to take an exclusive lock. I was also watching this table:
SELECT *
FROM sys.dm_tran_locks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply