Problem with Triggers SQL 2008 R2

If use the following code to check for the existance of a table. I put this code in a trigger just in case something happens to the table where the trigger writes data.

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Since none of the remaining code in the trigger runs I am assuming the problem to be that the IF NOT EXISTS returns true inside the trigger causing the "RETURN" command to be executed and my remaining code in the trigger never fires.

It seems to work fine in a query window of the SSMS.

This code results in EXISTS being printed.

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Can anyone shed some light on what is going on here?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Is the trigger in a table on the same db you are trying to write to?  That is, the same db that the EXISTS() is running in?

If not, the OBJECT_ID() function will not work as expected.

Try it this way:

IF NOT EXISTS (select * from database_name.dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]') and type = 'U')
spencerturbineAuthor Commented:
Yes the table that I have applied the trigger on is ttdsls040600 and is in the same database.
Scott PletcherSenior DBACommented:

Need to verify that this query is failing to properly check existence in the trigger.

For example, just for testing, do this inside the trigger:

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTableName]') and type_desc = 'USER_TABLE')
    RAISERROR('NOT EXISTS in trigger is true', 10, 1)

and see if you get that message when you cause the trigger to fire.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HainKurtSr. System AnalystCommented:
what do you get from this

select *,OBJECTPROPERTY(id, N'IsUserTable')
from dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]')
--and OBJECTPROPERTY(id, N'IsUserTable') = 1
spencerturbineAuthor Commented:
Well here is the issue....

Inside the trigger code I removed the IF NOT EXISTS so I can get some code to run.

I put this in:

SELECT @t_desc = name
      from baandb.dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]')

Then I inserted @t_desc to a new table I created and it is NULL

In a SSMS query window I run:

SELECT name from baandb.dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]')

and I get: ttdsls040600_NOTIFY  in the results.
Scott PletcherSenior DBACommented:
Hmm ... are you the user being used to run the statement that causes the trigger to fire?  Or is this part of some app that gets a different user assigned?

By default the trigger will run using the authority of the user that ran the statement.

If that user does not have authority to see the [dbo].[ttdsls040600_NOTIFY] table, then you could get the conditions you are seeing.

You may want to try adding EXECUTE AS on the trigger to make sure it always has sufficient authority to do whatever it needs to do.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spencerturbineAuthor Commented:
There is only one user of the database. All windows users are mapped through the application to that one database user.

This use does not have the db_owner role, nor is it the owner of the database. It is a member of a custom role created by the vendor. It has grant permissions only on SELECT,INSERT,DELETE, and UPDATE.
spencerturbineAuthor Commented:
Very interesting. I logged in via SQL Authentication as the one user and I cannot see any tables created by anyone other than the vendor.

So I immediately checked my custom table permissions in a windows authenticated connection and see that the baandbg role is not listed under the Users or Roles area for any of my tables.

I find it odd that I can insert into my tables from trigger code but I cannot check for their existance.

      from baandb.dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]') returned empty in the SQL Auth connection!!
Scott PletcherSenior DBACommented:
A user can be granted INSERT but not granted (or even explicitly denied) VIEW DEFINITION.

MS has worked hard to make permissions more granular in SQL Server.

I'm sure about now you really "appreciate" that, huh? :-)
spencerturbineAuthor Commented:
Well it sure has been a fun two days manually updating tables because my triggers wern't firing!
Scott PletcherSenior DBACommented:
I usually use 'sa' to run triggers that need high authority.  But you probably can't add 'sa' to the db and assign the trigger to run as sa :-(.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.