Solved

Problem with Triggers SQL 2008 R2

Posted on 2012-03-20
11
291 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:ScottPletcher
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:ScottPletcher
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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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:ScottPletcher
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now