August 10, 2004 at 10:37 pm
I have a table for expenses, and a view that limits columns and returns only the rows for the current user (w/ check option). I am trying to create a trigger on that view that rejects updates if the expense record has been "approved" (status of binary column). When I run the CREATE TRIGGER code, I keep getting the following error:
Server: Msg 208, Level 16, State 4, Procedure trgExpensesUpdate, Line 2
Invalid object name 'dbo.qryExpenses'.
If I try to create the trigger on the underlying table, it works OK. But when I run it on the view, even if I remove the WHERE clause & "WITH CHECK OPTION", It still throws the same error. Can anyone tell me what is going on? (sql for table, view, and trigger below)
CREATE TABLE [dbo].[tblExpenses] (
[Expense_PK] [int] IDENTITY (1, 1) NOT NULL ,
[TransDate] [datetime] NOT NULL ,
[PostDate] [datetime] NOT NULL ,
[Employee_FK] [int] NOT NULL ,
[BusArea_FK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpJID] [int] NOT NULL ,
[ExpDesc_FK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Merchant] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Amount] [money] NOT NULL ,
[Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Approve] [bit] NOT NULL ,
[Reimburse] [bit] NOT NULL ,
[Billed] [bit] NOT NULL ,
[Verified] [bit] NOT NULL ,
[Miles] [int] NULL ,
[FromLoc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ToLoc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReturnLoc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE VIEW dbo.qryExpenses
WITH VIEW_METADATA
AS
SELECT TransDate, Employee_FK, BusArea_FK, ExpJID, ExpDesc_FK, Merchant, Amount, Type, Miles, FromLoc, ToLoc, ReturnLoc, Notes, Expense_PK
FROM dbo.tblExpenses
WHERE (Employee_FK = dbo.fncEmpID(USER))
WITH CHECK OPTION
CREATE TRIGGER trgExpensesUpdate ON [dbo].[qryExpenses]
FOR UPDATE, DELETE
AS
DECLARE
@app binary
SELECT @app = e.approve
FROM dbo.tblExpenses e,
deleted d
WHERE e.Expense_PK = d.Expense_PK
IF (@app = 1)
BEGIN
RAISERROR ('Cannot Edit Approved Expense', 16, 1)
ROLLBACK TRANSACTION
END
August 11, 2004 at 4:48 am
Hi,
You can only use INSTEAD OF triggers on views with the WITH CHECK OPTION set. Personally I would create the trigger on the table. That way all entries points to the table will be covered.
Regards
Richard...
August 11, 2004 at 7:50 am
Even when I create the view without the WHERE clause, and without the "WITH CHECK CONSTRAINT" option (below), I still get the same error message. Also, I specifically want the trigger on the view, because I only want the trigger to apply when employees try to update/delete already approved expenses (employees can only access expenses in this view). Managers still need the ability to update the record even after it is approved.
CREATE VIEW dbo.qryExpenses
WITH VIEW_METADATA
AS
SELECT TransDate, Employee_FK, BusArea_FK, ExpJID, ExpDesc_FK, Merchant, Amount, Type, Miles, FromLoc, ToLoc, ReturnLoc, Notes, Expense_PK
FROM dbo.tblExpenses
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply