• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Trigger

Dear All,

I have created an INSERT trigger on production database which only copies the Inserted data into a stagging table for further processing. further more the stagging table does not contain any constraints, key or indexes. But the data is failing to insert into production table from application, where as if i run the query frrom profiler directly everything works fine...

Is there is any issues with cross database inserts from triggers??

Any suggestions??

Kind Regards,

Asim Shahzad
0
jaguar4u
Asked:
jaguar4u
  • 2
  • 2
1 Solution
 
sachinpatil10dCommented:
can you please tell the error details also the actual trigger containts
0
 
jaguar4uAuthor Commented:
Actually there is no error but som how the orignal transaction isnot saved....

What i thing is i should try removing the where clause from the trigger... but still the problem is there


ALTER TRIGGER [dbo].[INSERT_TO_TAMS]
   ON  [dbo].[TB_EVENT_LOG]
   AFTER  INSERT 
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	DECLARE @userID INT
	SELECT @userID = nuserid FROM inserted

	INSERT INTO tams.dbo.employee_event_transactions_BIOSTAR 
		([employee_id],
		[transaction_time],
		[reason_id],
		[remarks],
		[reader_id],
		[user_entry_flag],
		[tcn],
		[created_id],
		[created_date],
		[last_updated_id],
		[last_updated_date])
			SELECT 
				nUserID,
				DATEADD(SS,nDateTime, '01/01/1970 00:00:00') AS TDate,
				nTNAEvent,
				'',
				nReaderIdn,
				0,
				1000025,
				1,
				GETDATE(),
				1,
				GETDATE()

			FROM  inserted  
			WHERE nTNAEvent in (0,1,2,3) 

END

Open in new window

0
 
sachinpatil10dCommented:
yep, remove the where clause an check

what is the purpose of below lines
      DECLARE @userID INT
      SELECT @userID = nuserid FROM inserted

if no use remove that too
0
 
jaguar4uAuthor Commented:
Have removed the where clause and the blow lines as well..

     DECLARE @userID INT
      SELECT @userID = nuserid FROM inserted

But still no luck, any more suggestions.

Is there is any database level restrictions on cross database trigger inserts?
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now