Insert Data into another database failed-- in Trigger -- sql server programming

Omnibuzz
4/24/2006 11:07:02 PM

also write your catch block like this

BEGIN CATCH
rollback tran
INSERT INTO ErrorMsg VALUES (
ltrim(str(error_number())),error_message())
END CATCH

[quoted text, click to view]

Elvin
4/24/2006 11:31:01 PM

Hi,
I have take out the [Server1] but still no luck, when there is a inserted
record in table1, it still cannot insert into table2 and this cause no record
have insert into table1 also. If i change to same database table then it
work...any clue...

ALTER TRIGGER [Trigger1]
ON [dbo].[tblTable1]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ResourceID int
DECLARE @Call_Type varchar(8)
DECLARE @Destination_Type varchar(20)

SELECT
@ResourceID=ResourceID,@Call_Type=Call_Type,@Destination_Type=Destination_
Type FROM inserted

IF @Call_Type='Outgoing'
BEGIN TRY
INSERT INTO [Server1].[DB1].[dbo].[Table2]
([ResourceID]
,[Call_Type]
,[Destination_Type])
VALUES
(@ResourceID,
@Call_Type ,
@Destination_Type)
END TRY
BEGIN CATCH
INSERT INTO ErrorMsg VALUES (
ltrim(str(error_number())),error_message())
END CATCH
END

[quoted text, click to view]

Elvin
4/24/2006 11:55:02 PM

The problem is not able to insert record into another database
([Server1].[DB1].[dbo].[Table2]) when there is a inserted event in the
trigger, i have try create a same table into a same database then it work
([dbo].[Table2]) , so the question is how to allow the trigger insert a
record into another database table when there is a inserted event?

[quoted text, click to view]

Uri Dimant
4/25/2006 12:00:00 AM

Elvin
[quoted text, click to view]

INSERT INTO Database.dbo.Table (columns here) VALUES (data here)

[quoted text, click to view]

Tony Rogerson
4/25/2006 12:00:00 AM

Change
INSERT INTO [Server1].[DB1].[dbo].[Table2]

To
INSERT INTO [DB1].[dbo].[Table2]

Please post the error you are getting.

Note, no error will be inserted into ErrorMsg because the whole trigger is
inside a transaction and your failure will cause it to roll back.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

[quoted text, click to view]

Uri Dimant
4/25/2006 12:00:00 AM

Elvin
What is the error?

[quoted text, click to view]

Tony Rogerson
4/25/2006 12:00:00 AM

Hi Elvin,

Like I said, the error in the trigger will cause the insert into your
ErrorMsg and everything else to rollback.

In your catch, put this statement...

PRINT error_message()

That will hopefully capture the error to the screen so you can post it here.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Insert Data into another database failed-- in Trigger