A TRIGGER IS CHOKING UP THE TABLE?
- Dear folks,We have got an issue and we can't work out. There is a table which own three triggers, (for insert, for update and for delete actions). Well, when ASP solution launch an update over that table and then trigger is requested, that mechanism, obviously, do an update again and then, IIS is not be able to retrieve that information back to the asp, causing time-out for the end-user. As a matter of fact, we don't see anything abnormal in the processes panel.Does aynone ever used or experienced something alike to this?thanks a lot for any input or rectification as well as thoughts or ideas
-
Perhaps something in the trigger is causing blocking, ultimately causing the process to terminate. Check sql logs to see if deadlocks are occuring.
you can also isolate locking in Sql profiler.
without your code its hard to say the cause.
-
Sorry for that. Here you've got it:
CREATE TABLE [dbo].[Via_Peticiones] (
[CodPeticion] [char] (12) NOT NULL ,
[Estado] [char] (1) NOT NULL ,
[Dni] [char] (10) NOT NULL ,
[Nombre] [varchar] (255) NULL ,
[EMail] [varchar] (50) NULL ,
[Unidad] [char] (4) NULL ,
[DniResp] [char] (10) NULL ,
[NombreResp] [varchar] (255) NULL ,
[EMailResp] [varchar] (50) NULL ,
[UnidadResp] [char] (4) NULL ,
[ComentAprobDeneg] [text] NULL ,
[NombreViajero] [varchar] (255) NULL ,
[Recogida] [char] (1) NULL ,
[OfiEnvio] [char] (4) NULL ,
[FechaCompromiso] [datetime] NOT NULL ,
[FechaFinCompromiso] [datetime] NOT NULL ,
[Destino] [varchar] (50) NOT NULL ,
[Origen] [varchar] (50) NOT NULL ,
[MedioSalidaSolic] [varchar] (50) NULL ,
[Alojamiento] [char] (1) NULL ,
[Comentarios] [varchar] (512) NULL ,
[ComentariosAgencia] [varchar] (512) NULL ,
[FechaSalida] [datetime] NULL ,
[FechaRegreso] [datetime] NULL ,
[MTSalida] [varchar] (50) NULL ,
[MTRegreso] [varchar] (50) NULL ,
[Hotel] [varchar] (100) NULL ,
[FechaFactura] [datetime] NULL ,
[Precio] [money] NOT NULL ,
[TipoViaje] [char] (1) NOT NULL ,
[CodCurso] [char] (6) NULL ,
[Centro] [char] (8) NULL ,
[Acto] [char] (8) NULL ,
[Subacto] [char] (8) NULL ,
[Programa] [char] (7) NULL ,
[Autorizacion] [char] (6) NULL ,
[Compromiso] [char] (6) NULL ,
[FechaAlta] [TFechaRegistro] NULL ,
[FechaAprobacion] [datetime] NULL ,
[FechaResolucion] [datetime] NULL ,
[IdFactura] [char] (20) NULL ,
[DniViajero] [char] (10) NULL ,
[MedioRegresoSolic] [varchar] (50) NULL ,
[FechaEmision] [datetime] NULL ,
[DNIAprob] [char] (10) NULL ,
[PrecioDesp] [money] NULL ,
[PrecioAloj] [money] NULL ,
[UsuConectado] [varchar] (12) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Via_Peticiones] ADD
CONSTRAINT [DF_Via_Peticiones_Estado] DEFAULT ('(A)') FOR [Estado],
CONSTRAINT [DF_Via_Peticiones_Precio] DEFAULT (0) FOR [Precio],
CONSTRAINT [DF_Via_Peticiones_TipoViaje] DEFAULT ('C') FOR [TipoViaje],
CONSTRAINT [DF_Via_Peticiones_PrecioDesp] DEFAULT (0) FOR [PrecioDesp],
CONSTRAINT [DF_Via_Peticiones_PrecioAloj] DEFAULT (0) FOR [PrecioAloj],
CONSTRAINT [PK_Via_Peticiones] PRIMARY KEY NONCLUSTERED
(
[CodPeticion]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
setuser
GO
EXEC sp_bindefault N'[dbo].[DefFechaRegistro]', N'[Via_Peticiones].[FechaAlta]'
GO
setuser
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER Via_Peticiones_TI ON dbo.Via_Peticiones
FOR INSERT
AS
DECLARE @Tipo CHAR(1)
DECLARE @UsuarioNT Varchar(255)
IF @@ROWCOUNT>1 SELECT @Tipo='I' ELSE SELECT @Tipo='A'
UPDATE Via_Peticiones SET NombreViajero=p.Nombre
FROM Via_Peticiones p INNER JOIN Inserted i ON p.CodPeticion=i.CodPeticion
WHERE i.NombreViajero IS NULL OR i.NombreViajero=''
SET ROWCOUNT 1
SELECT @UsuarioNT=nt_username FROM master.dbo.sysprocesses WHERE hostname=host_name()
SET ROWCOUNT 0
UPDATE Via_Peticiones SET Precio=0, PrecioDesp=0, PrecioAloj=0, FechaAlta=GetDate()
FROM Via_Peticiones p INNER JOIN Inserted i ON p.CodPeticion=i.CodPeticion
INSERT INTO Via_HistoricoCambios
(CodPeticion, Dni, Nombre, EMail, Unidad, DniResp, NombreResp, EMailResp, UnidadResp, FechaCompromiso, FechaFinCompromiso, Destino, Origen, MedioSalidaSolic,MedioRegresoSolic, Alojamiento,
Comentarios, ComentariosAgencia, Precio, PrecioDesp, PrecioAloj, Estado, TipoViaje, CodCurso, FechaAlta, FechaAprobacion, FechaResolucion,
Centro, Acto, SubActo, Programa, Autorizacion, Compromiso, FechaSalida, FechaRegreso, MTSalida, MTRegreso, Hotel, FechaFactura,
OfiEnvio, NombreViajero, IDFactura, DNIAprob,
ctrl_Tipo, ctrl_Usuario, ctrl_Aplicacion, ctrl_Fecha, ctrl_Host, ctrl_UsuarioNT)
SELECT CodPeticion, Dni, Nombre, EMail, Unidad, DniResp, NombreResp, EMailResp, UnidadResp, FechaCompromiso, FechaFinCompromiso, Destino, Origen, MedioSalidaSolic,MedioRegresoSolic, Alojamiento,
Comentarios, ComentariosAgencia, Precio, PrecioDesp, PrecioAloj, Estado, TipoViaje, CodCurso, FechaAlta, FechaAprobacion, FechaResolucion,
Centro, Acto, SubActo, Programa, Autorizacion, Compromiso, FechaSalida, FechaRegreso, MTSalida, MTRegreso, Hotel, FechaFactura,
OfiEnvio, NombreViajero, IDFactura, DNIAprob,
@Tipo,user_name(),RIGHT(app_name(),6),getdate(),HOST_NAME(),@UsuarioNT FROM Inserted
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Trigger dbo.NombreDeLaTabla_TriggerUpdate Script Date: 3/12/98 17:20:32 ******/
CREATE TRIGGER Via_Peticiones_TU ON dbo.Via_Peticiones
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @Tipo CHAR(1)
DECLARE @UsuarioNT Varchar(255)
IF @@ROWCOUNT>1 SELECT @Tipo='U' ELSE SELECT @Tipo='E'
SET ROWCOUNT 1
SELECT @UsuarioNT=nt_username FROM master.dbo.sysprocesses WHERE hostname=host_name()
SET ROWCOUNT 0
UPDATE Via_Peticiones SET Precio=ISNULL(i.PrecioDesp+i.PrecioAloj, 0)
FROM Inserted i INNER JOIN Via_Peticiones p ON p.CodPeticion=i.CodPeticion
WHERE ISNULL(i.Precio, 0) <> ISNULL(i.PrecioDesp+i.PrecioAloj, 0)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-
*can you specify with nolock for
SELECT .....
FROM master.dbo.sysprocesses WHERE hostname=host_name()
we don't want locking to occur in system tables
*for CREATE TRIGGER Via_Peticiones_TI ON dbo.Via_Peticiones
FOR INSERT
AS
specify SET NOCOUNT ON
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply