Solved

Problem with Triggers SQL 2008 R2

Posted on 2012-03-20
11
296 Views
Last Modified: 2012-03-21
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
Comment
Question by:spencerturbine
  • 5
  • 5
11 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37744116
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
 

Author Comment

by:spencerturbine
ID: 37744152
Yes the table that I have applied the trigger on is ttdsls040600 and is in the same database.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37744321
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 51

Expert Comment

by:HainKurt
ID: 37744381
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
 

Author Comment

by:spencerturbine
ID: 37744813
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37744845
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
 

Author Comment

by:spencerturbine
ID: 37747380
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
 

Author Comment

by:spencerturbine
ID: 37747524
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37747658
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
 

Author Comment

by:spencerturbine
ID: 37748121
Well it sure has been a fun two days manually updating tables because my triggers wern't firing!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37748214
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question