Need Help In Trigger

Question

Need Help In Trigger

abmore

Hall of Fame

Points: 3628
More actions
November 22, 2007 at 10:43 pm

#70517

Hi ,
I had created one view, which is derived from table from diff. DB on the same server.
Now I m creating trigger on the view it is giving an ERROR
" The object % does not exist or is invalid for this operation."
????
Regards,
Abhijit

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the text of the view and the trigger please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abmore Hall of Fame Points: 3628 More actions · Answer 2

abmore

Hall of Fame

Points: 3628

November 23, 2007 at 12:15 am

#753535

Text of the view contains only...

SELECT *

FROM

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Select * from ..... ????

And the trigger?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abmore Hall of Fame Points: 3628 More actions · Answer 4

sorry for d wrong reply...

Select * from of object on diff DB.

& trigger contains..... inserting record in history table, which schema is same.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you please post the code? You can change the table and column names if needed. It's hard to debug from descriptions

Is the history table in the DB with the view, or the DB that the view is referencing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abmore Hall of Fame Points: 3628 More actions · Answer 6

Create table dbo.tmpTriggerTest

(

IDINT IDENTITY(1, 1),

Value1VARCHAR(10)

)

GO

Create table dbo.tmpTriggerTestHistory

(

IDINT,

Value1VARCHAR(10)

)

GO

Create View dbo.vw_tmpTriggerTest

AS

SELECT * FROM dbo.tmpTriggerTest

GO

Consider tat tmpTriggerTest is a table from different DB on same server.

I had created view namely vw_tmpTriggerTest which is deriveed from table

tmpTriggerTest. Now, in SQL server we can create trigger on view i want to create trigger on view created.

any queries plz revert. 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

The view as you've written it will select from a table in the same database as it is created. To select from a table in a different database, it should read

Create View dbo.vw_tmpTriggerTest

AS

SELECT * FROM OtherDB.dbo.tmpTriggerTest

GO

(Replace OtherDB with the name of the database that the table is in)

Do you have the trigger code please? Is the history table in the same DB as the table, or as the view?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abmore Hall of Fame Points: 3628 More actions · Answer 8

Hi !,

I have written the view in the same way as you had mentioned.

I have added the one trigger for insert, update, delete operation, in which i just insert the inserted , updated, deleted row in history table

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

This is hard work, isn't it? You've been asked four times now to post the code of the trigger. You have to help yourself if you expect anyone else to help you.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

When the exact code of the view and the exact code of the trigger appear, I'll help you. Until then, you're wasting my time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abmore Hall of Fame Points: 3628 More actions · Answer 11

Hi gail i m not wasting any person time, i have already explained you the view and the trigger.

but its k.. i got my answer thxs for your valueable time and replies

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Explanations don't help much when debugging code. That's why I kept asking for the code of the trigger and view.

I had an idea what might be wrong, but without seeing the actual trigger, I was just guessing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

compusridhar SSC Journeyman Points: 90 More actions · Answer 13

compusridhar

SSC Journeyman

Points: 90

April 25, 2009 at 9:08 pm

#984022

Hi Abjhit,

How did you solve it

compusridhar SSC Journeyman Points: 90 More actions · Answer 14

Use "Instead OF" trigger instead of "FOR" trigger... Actually microsofts error message is misleading