Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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)
RETURN

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)
      BEGIN
      PRINT 'NOT EXISTS'
      END
      ELSE
      BEGIN
      PRINT 'EXISTS'
      END

Can anyone shed some light on what is going on here?
0
spencerturbine
Asked:
spencerturbine
  • 5
  • 5
1 Solution
 
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')
0
 
spencerturbineAuthor Commented:
Yes the table that I have applied the trigger on is ttdsls040600 and is in the same database.
0
 
Scott PletcherSenior DBACommented:
Hmm.

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')
BEGIN
    RAISERROR('NOT EXISTS in trigger is true', 10, 1)
    RETURN
END --IF


and see if you get that message when you cause the trigger to fire.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
0
 
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.
0
 
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.
0
 
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.
0
 
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.

SELECT name
      from baandb.dbo.sysobjects where id = object_id(N'[dbo].[ttdsls040600_NOTIFY]') returned empty in the SQL Auth connection!!
0
 
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? :-)
0
 
spencerturbineAuthor Commented:
Well it sure has been a fun two days manually updating tables because my triggers wern't firing!
0
 
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 :-(.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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