September 6, 2009 at 12:39 am
Hi All,
I have created a Insert Trigger and a Update Trigger on a table in a database in SQL Server 2000. The Insert trigger will make an insert into another table whenever an insert happens on the table on which trigger is created.
Now an external application (developed in .NET and I have no source code access) makes an insert successfully in the table. But the trigger doesn't fire.
When I directly execute a insert script on the table from SQL Query Analyzer, then the trigger fires but when insert is done by the external application (.NET app) the trigger doesn't fire.
Can somebody help me out with this...I'm completely clueless as to why it is behaving this way.
Thanks
Debsoft
September 6, 2009 at 7:20 am
Pls. provide the Full script of that trigger along with the table structure.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
September 6, 2009 at 10:19 am
Triggers fire, but depending on what you've coded, perhaps you're missing something. Showing the details will help us decode it.
also, have you checked the code that is used in the .NET application? Are you sure it's the same code you're using in SSMS?
September 6, 2009 at 10:56 am
start sqlprofiler and ask your .net dev to perform his (her) inserts.
Check the statements your profiler shows and you'll have your diagnose.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 6, 2009 at 12:51 pm
Here goes the trigger code...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER TRIGGER [dbo].[InsertReservationTrigger] ON [Euronext].[dbo].[Reservations]
After Insert
AS
--Declare @Max_ReservationID bigint
Begin
--Select @Max_ReservationID = max(Reservation_ID) from Euronext.dbo.Reservations
Insert into VNOCTEMP.dbo.Reservations
(
[Reservation_ID],
[ClientName],
[Group_ID],
[Meeting Start],
[Meeting End],
[Actual Start],
[Actual End],
[DateCreated],
[DateModified],
[Recurrence_ID],
[Room_ID],
[Recurrence],
[Number of Attendees/Resource Name 1],
[Number of Attendees/Resource Name 2],
[Number of Attendees/Resource Name 3],
[Number of Attendees/Resource Name 4],
[Number of Attendees/Resource Name 5],
[Number of Attendees/Attendees in 1],
[Number of Attendees/Attendees in 2],
[Number of Attendees/Attendees in 3],
[Number of Attendees/Attendees in 4],
[Number of Attendees/Attendees in 5],
[General/TPG Required],
[General/Host],
[General/Client Name],
[General/Client Code],
[General/Notes],
[General/Reservation Type],
--[General/Meeting Title],
[General/Presentation Resource],
[General/Business Unit],
[General/Participant PIN],
[General/Chairperson],
[General/Booked By],
[General/Type],
127.0.0.1,
127.0.0.1,
127.0.0.1,
[ISDN Information/ISDN Number1],
[ISDN Information/ISDN Number2],
[ISDN Information/ISDN Number3],
[ISDN Information/ISDN Number4],
[ISDN Information/ISDN Number5],
[ISDN Information/ISDN Number6],
[ISDN Information/ISDN Number7],
[ISDN Information/ISDN Number8],
[ISDN Information/ISDN Number9],
[ISDN Information/ISDN Number10],
[ISDN Information/ISDN Number11],
[ISDN Information/ISDN Number12],
[Number of Attendees/TPG Endpoint1],
[Number of Attendees/TPG Endpoint2],
[Number of Attendees/TPG Endpoint3],
[Number of Attendees/TPG Endpoint4],
[Number of Attendees/TPG Endpoint5],
[Number of Attendees/TPG Endpoint6],
[Number of Attendees/TPG Endpoint7],
[Number of Attendees/TPG Endpoint8],
[Number of Attendees/TPG Endpoint9],
[Number of Attendees/TPG Endpoint10],
[Number of Attendees/TPG Endpoint11],
[Number of Attendees/TPG Endpoint12]
)
Select
[Reservation_ID],
'Euronext' as [ClientName],
[Group_ID],
[Meeting Start],
[Meeting End],
[Actual Start],
[Actual End],
[DateCreated],
[DateModified],
[Recurrence_ID],
[Room_ID],
[Recurrence],
[Number of Attendees/Resource Name 1],
[Number of Attendees/Resource Name 2],
[Number of Attendees/Resource Name 3],
[Number of Attendees/Resource Name 4],
[Number of Attendees/Resource Name 5],
[Number of Attendees/Attendees in 1],
[Number of Attendees/Attendees in 2],
[Number of Attendees/Attendees in 3],
[Number of Attendees/Attendees in 4],
[Number of Attendees/Attendees in 5],
[General/TPG Required],
[General/Host],
[General/Client Name],
[General/Client Code],
null as [General/Notes],
[General/Reservation Type],
--convert(char(255), [General/Meeting Title]) as [General/Meeting Title],
[General/Source] as [General/Presentation Resource],
[General/Business Unit],
null as [General/Participant PIN],
[General/Chairperson],
[General/Booked By],
[General/Type],
127.0.0.1,
127.0.0.1,
127.0.0.1,
[ISDN Information/ISDN Number1],
[ISDN Information/ISDN Number2],
[ISDN Information/ISDN Number3],
[ISDN Information/ISDN Number4],
[ISDN Information/ISDN Number5],
[ISDN Information/ISDN Number6],
[ISDN Information/ISDN Number7],
[ISDN Information/ISDN Number8],
[ISDN Information/ISDN Number9],
[ISDN Information/ISDN Number10],
[ISDN Information/ISDN Number11],
[ISDN Information/ISDN Number12],
[Number of Attendees/TPG Endpoint1],
[Number of Attendees/TPG Endpoint2],
[Number of Attendees/TPG Endpoint3],
[Number of Attendees/TPG Endpoint4],
[Number of Attendees/TPG Endpoint5],
[Number of Attendees/TPG Endpoint6],
[Number of Attendees/TPG Endpoint7],
[Number of Attendees/TPG Endpoint8],
[Number of Attendees/TPG Endpoint9],
[Number of Attendees/TPG Endpoint10],
[Number of Attendees/TPG Endpoint11],
[Number of Attendees/TPG Endpoint12]
from inserted
--from Euronext.dbo.Reservations
--where Reservation_ID = @Max_ReservationID
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
September 6, 2009 at 2:31 pm
the trigger itself looks ok, but it could still fail if any of the fields you are inserting are not null, but the data is null. That's where i would look.
also, you originally posted that it was for insert or update, but the trigger you pasted is for insert only.
Lowell
September 6, 2009 at 11:52 pm
- on the other hand, if the trigger fails, the whole transaction fails, so the insert will be undone !
- double check that the triggers are enabled !
SELECT OBJECT_NAME(parent_obj) tablename
, [name] triggername
, (CASE
WHEN (STATUS & 2048) = 2048 THEN 'Disabled'
ELSE 'Enabled'
END) state
FROM sysobjects
WHERE xtype = 'TR'
order by 1, 2
;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply