Table Does Not Exist Or Is Invalid...

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

General SQL Server Forums

New to SQL Server Programming

Table Does Not Exist Or Is Invalid...

Author

Topic

Matt-21
Starting Member

11 Posts

Posted - 2009-07-13 : 12:36:09

I am trying to create a trigger that will send me an email if certain fields in a view have changed. When I run the sql I get the following error

Msg 8197, Level 16, State 6, Procedure cusAdd, Line 1
The object 'dbo.customeraddresses' does not exist or is invalid for this operation.

the view is there and I have done a select statement to prove I have not made any spelling errors in the table name.

This seems a similar problem to TOPIC_ID=99076 except the owner of the view is dbo as with my other tables

Matt

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-13 : 13:00:20

can you post the trigger code as well as your test select statement?

EDIT:
are you getting this error when you try to compile the trigger or when it fires via the table operation?

Be One with the Optimizer
TG

Matt-21
Starting Member

11 Posts

Posted - 2009-07-13 : 13:23:06

I am getting the error when trying to compile the trigger

My Select statement is very basic: SELECT * from customeraddresses

This is my first trigger so I am expecting my sql to be incorrect but it seems to error at the table before it gets to the trigger itself. The trigger will be more detailed (which is why I have declared more fields then I am currently using. Anyway my trigger code is as follows;

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER cusAdd
ON dbo.customeraddresses
AFTER UPDATE
AS
DECLARE @id [nvarchar](20)
DECLARE @name [nvarchar](127)
DECLARE @addressid [nvarchar](30)
DECLARE @countryid [nvarchar](3)
DECLARE @currencyid [nvarchar](3)

SET @id = (SELECT customer FROM updated)
SET @name = (SELECT name FROM updated)
SET @addressid = (SELECT addressid FROM updated)
SET @countryid = (SELECT countryid FROM updated)
SET @currencyid = (SELECT currencyid FROM updated)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT customeraddresses.countryid, customeraddresses.currencyid
FROM inserted i, deleted d
IF
i.countryid <> d.countryid
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Customer "' + @id + @name + '" has changed its country code' + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'my email address', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END

END
GO

Matt

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-13 : 13:27:48

no such table "updated". inserted and deleted are valid


SELECT	@id = customer,
	@name = name,
	@addressid = addressid.
 	@countryid = countryid,
	@currencyid = currencyid
FROM	INSERTED

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-13 : 13:28:46

you can't create a trigger on a view (except for instead of trigger). just on a table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:35:21

why cant you do this in a procedure rather than in trigger. may be schedule a job to execute the sp at regular intervals

Matt-21
Starting Member

11 Posts

Posted - 2009-07-14 : 05:32:06

Thanks for the help everyone.

Taking your point Russell I am now using a table for my trigger. However I still get the same error when using the table.

To try and eliminate the issue of it being the code (I can work on the SQL once I know I can get the trigger to work). I have created a simple trigger based on an insert.

create trigger counrtyCode ON dbo.traderaddresses AFTER INSERT AS

DECLARE @countryid [nvarchar](3)

SET @countryid = (SELECT countryid FROM inserted)

If @countryid <> 'GB'
BEGIN
PRINT 'Email Code'
END

my error:
Msg 8197, Level 16, State 4, Procedure counrtyCode, Line 1
The object 'dbo.traderaddresses' does not exist or is invalid for this operation.

Thanks again

Matt

Matt

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-14 : 05:38:59

you try to execute an operation for a non-existant object or the operation is invalid for this type of object.

CREATE TRIGGER COUNRTYCODE ON DBO.TRADERADDRESSES
AFTER INSERT AS
DECLARE @COUNTRYID NVARCHAR(3)

SELECT
@COUNTRYID = COUNTRYID
FROM INSERTED

IF @COUNTRYID <> 'GB'
BEGIN
PRINT 'EMAIL CODE'
END

-------------------------
R..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 05:39:13

Looks like traderaddresses does not exist or does not exist in the database where are trying to install the trigger.
And this "SET @countryid = (SELECT countryid FROM inserted)" will not work if there is more than one record in INSERTED.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-14 : 10:05:51

Can you post the DDL for dbo.traderaddresses?

Matt-21
Starting Member

11 Posts

Posted - 2009-07-14 : 10:15:15

Done it! well part at least I have a trigger that will email me on change of field (on a table not a view). Now I have this working I will look at bringing in fields from other tables to make my emails more meaningful.

Thanks again

Matt

Matt

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-14 : 10:19:45

Great! So, what did you change to resolve this (besides triggering the table instead of the view)?

Matt-21
Starting Member

11 Posts

Posted - 2009-07-14 : 10:42:31

The table was tradersaddresses NOT traderaddresses for which I feel a reel idiot but once I had got some basic code to work, I could concentrate on the sql.

It is still in development but currently looks like this and its working;

CREATE TRIGGER [dbo].[counrtyCode] ON [dbo].[tradersaddresses]
AFTER UPDATE AS
DECLARE @i_countryid NVARCHAR(3)
DECLARE @i_traderid NVARCHAR(20)
DECLARE @i_tradertype NVARCHAR(2)
DECLARE @i_user NVARCHAR(36)

SELECT
@i_countryid = countryid,
@i_traderid = traderid,
@i_tradertype = tradertype,
@i_user = modifiedby
FROM INSERTED

DECLARE @d_countryid NVARCHAR(3)

SELECT
@d_countryid = countryid
FROM DELETED

IF @i_countryid <> @d_countryid AND @i_tradertype = 'C'
BEGIN
DECLARE @custmsg varchar(500)
SET @custmsg = 'Customer "' + @i_traderid +
'" has changed its country code to "' + @i_countryid +'".'+ ' Modified by User: "' + @i_user + '".'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'my emial, @body= @custmsg, @subject = 'Customer SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END

IF @i_countryid <> @d_countryid AND @i_tradertype = 'S'
BEGIN
DECLARE @suppmsg varchar(500)
SET @suppmsg = 'Supplier "' + @i_traderid +
'" has changed its country code to "' + +'".'+ ' Modified by User: "' + @i_user + '".'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'my email', @body= @suppmsg, @subject = 'Supplier SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END

Matt

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-14 : 10:44:58

yeah, that'll do it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources