Trigger creation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Trigger creation problem

Author

Topic

metazone
Starting Member

3 Posts

Posted - 2008-04-08 : 09:45:18

Very new to SQL Server - using Microsoft SQL Server Management Studio, I'm trying to create a Trigger but I get an error saying that the table doesn't exist or is invalid -- "Object 'dbo.tblPropertyValue' does not exist or is invalid for this operation". The table does exist. Following is the trigger (I replaced the actual code w/ a simple PRINT statement):

CREATE TRIGGER [dbo].[tr_PropValChange] ON [dbo].[tblPropertyValue] FOR UPDATE AS
BEGIN
PRINT 'Hello'
END
GO

I'm able to create a stored procedure that accesses that table. The database (MyDb) is running on my laptop (computer name=MyComputer) and owned by my windows account (MyComputer\Bill). All db tables are prefixed with 'dbo.'. I connect to Management Studio using the sa id. We're using SQL Server 2005 (Product Version: 9.00.1406.00, Product Level: RTM, Edition: Developer Edition).

Thanks,
Bill

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 09:50:57

So What is your question?

See create Trigger in BOL.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 09:55:58

Are you sure it's not a view or you're in the wrong database?

try
select * from sysobjects where xtype = 'U' and name = 'tblPropertyValue'
select top 1 * from [dbo].[tblPropertyValue]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

metazone
Starting Member

3 Posts

Posted - 2008-04-08 : 10:08:00

[quote]Originally posted by nr

Are you sure it's not a view or you're in the wrong database?
[quote]

Thanks, I tried the select statement and it returned the info correctly. I assume it's a permissions problem and I'm researching how to change permissions w/ SQL Server Management Studio (any tips would be appreciated).

Thanks,
Bill

metazone
Starting Member

3 Posts

Posted - 2008-04-08 : 14:27:26

Note - I was trying to add a trigger in SQL Server Management Studio on the tblPropertyValue table, using SQL but SQL Server Management Studio returned an error that tblPropertyValue didn't exist. I then tried to add the following at the top of the SQL that contained the CREATE TRIGGER SQL:

use MyDb;

but I received an error saying that wasn't allowed in the same SQL as the CREATE TRIGGER command. I didn't know a really simple thing -- to just issue the above command separately as follows in SQL Server Management Studio in order to tell SQL Server Management Studio to use that database:

use MyDb;
GO

Once I did that, I then just used the CREATE TRIGGER command in a separate SQL statement:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources