CODEWITHSUNDEEP
sql-servertriggers
Tim Perry
Tim Perry

Reputation: 3096

SQL Server won't create trigger on table I can query

The command:

select * from dbo.hmg_cahplots

returns 9250 rows. However, when I try to create a trigger, it fails with:

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

Trigger code is:

CREATE TRIGGER dbo.LotUpdateTrigger_tdp ON dbo.hmg_cahplots FOR UPDATE, INSERT 
AS
BEGIN
    update lot
    set lot.hmg_planmodelname = model.hmg_modelname, lot.hmg_thermslotincentive = model.hmg_thermsincentive,
        lot.hmg_thermslotincentive_base = model.hmg_thermsincentive_base, lot.hmg_kwlotincentive = model.hmg_kwincentive
    from hmg_cahplots as lot inner join i
            on lot.hmg_cahplotsid = i.hmg_cahplotsid
         inner join hmg_pgecahp as proj 
            on proj.hmg_pgecahpid = lot.hmg_pgecahplots
         left outer join hmg_pgecahpmodels as model 
            on model.hmg_pgecahpmodelsid = lot.hmg_cahpplanstolotsid
            and model.hmg_pgecahpplansid = lot.hmg_pgecahplots         
END

I doubt this is very hard to solve. I assume I need to specify a namespace or something. However, I'm new to SQL Server and I don't have any idea how to start on this.

Thanks -- Tim

Upvotes: 1

Views: 9540

Answers (3)

Aske_reitz
Aske_reitz

Reputation: 11

Two problems:

Triggers are mostly Schema-locked.

You are using more than one.

use the same schema and ADD:

-- AT start add the code

USE [DATABASE] --switch with database name
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--At the END add 
END
GO

Upvotes: 1

user194076
user194076

Reputation: 9017

  1. Are you sure you are located in correct database, not master?
  2. Are you sure your permissions are fine?
  3. Are you sure this is a table, not a view?

Upvotes: 10

Michał Powaga
Michał Powaga

Reputation: 23183

If you are sure that this table exists and you are trying to create trigger in the same database, then remove coma just before from and after lot.hmg_kwlotincentive = model.hmg_kwincentive,.

Upvotes: 2

Related Questions