April 27, 2010 at 1:40 pm
I am a newbee and trying triggers for the first time to update a column in the same table once a new record is inserted(if clPDBtypes=4 then clPDBtype='ML'). I 'm getting errors when I try to execute the code as follows:
--
Msg 207, Level 16, State 1, Procedure trgIns_PDBtype, Line 19
Invalid column name 'clPDBtypes'
--
the column name is valid! Here is the code I wrote
--
ALTER TRIGGER [dbo].[trgIns_PDBtype]
ON [dbo].[tbl_clPDB]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.tbl_clPDB
SET clPDBtype='ML'
FROM inserted
Where Inserted.clPDBtypes=4;
END
----
I am puzzled and nay help is welcome.
NeilO
April 27, 2010 at 1:54 pm
It looks to me like you may have added an 's'
Where Inserted.clPDBtypes=4;
just a quess but I assume this should be type not types
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 27, 2010 at 1:56 pm
never mind I read you initial description wrong. I did not see the s the first time. those names are way to similiar. lol
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 27, 2010 at 2:06 pm
Could you post the DDL (CREATE TABLE statement) for the table, some sample data (INSERT INTO tablename statements) with which to test.
April 27, 2010 at 2:33 pm
Lynn - Here is the code, I did underscore the two items involved
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_clPDB](
[clPDB_ID] [int] IDENTITY(1,1) NOT NULL,
[clPDB_DOE] [datetime] NOT NULL CONSTRAINT [DF_tbl_clPDB_clPDB_DOE] DEFAULT (getdate()),
[clPDB_DOM] [datetime] NOT NULL CONSTRAINT [DF_tbl_clPDB_clPDB_DOM] DEFAULT (getdate()),
[clPDB_Active] [bit] NOT NULL CONSTRAINT [DF_tbl_clPDB_clPDB_Active] DEFAULT ((1)),
[clPDB_EmpPin] [varchar](6) NOT NULL,
[clPDB_NHSSN] [varchar](9) NOT NULL,
[clPDB_Claim_N] [varchar](50) NOT NULL,
[clPDB_InjDte] [datetime] NULL,
[clPDB_ResState] [varchar](2) NOT NULL,
[clPDB_InjState] [varchar](2) NOT NULL,
[clPDB_Emplyr] [varchar](50) NOT NULL,
[clPDB_pTypes] [int] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_pTypes] DEFAULT ((1)),
[clPDB_pType] [varchar](2) NULL CONSTRAINT [DF_tbl_clPDB_clPDB_pType] DEFAULT ('SG'),
[clPDB_Payer] [varchar](30) NULL,
[clPDB_DIB_MOE] [varchar](1) NULL CONSTRAINT [DF_tbl_clPDB_clPDB_DIB_MOE1] DEFAULT ((0)),
[clPDB_OFC1] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_DIB_MOE] DEFAULT ((1)),
[clPDB_PRIOR] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_DelICF1] DEFAULT ((0)),
[clPDB_OFC2] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_OFC11] DEFAULT ((0)),
[clPDB_PEND] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_PayType] DEFAULT ((0)),
[clPDB_pStart] [datetime] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_Pend1_1] DEFAULT ((0)),
[clPDB_PayType] [varchar](1) NULL CONSTRAINT [DF_tbl_clPDB_clPDB_Pend] DEFAULT ((0)),
[clPDB_APPEAL] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_APPEAL] DEFAULT ((0)),
[clPDB_NEXT] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_MorePDB] DEFAULT ((0)),
[clPDB_DelICF] [bit] NULL CONSTRAINT [DF_tbl_clPDB_clPDB_DelICF] DEFAULT ((0)),
CONSTRAINT [PK_tbl_clPDB] PRIMARY KEY CLUSTERED
(
[clPDB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
April 27, 2010 at 2:36 pm
It looks like you forgot the "_" in the field name in your trigger.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 27, 2010 at 2:39 pm
sorry posted on wrong thread
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 27, 2010 at 2:51 pm
You were right there was a couple of typos(the usal gotcho!).
I there a way of doing the update only to the new row and every row?
April 27, 2010 at 3:35 pm
This is not a very sensible use of a trigger in my opinion. Supplying a default value for one column isn't much justification for the overhead and inconvenience of a trigger. Use a regular stored procedure to do it, together with a constraint and/or a default if you like.
Try to avoid using triggers to modify data except as a last resort.
April 27, 2010 at 3:51 pm
Thanks but....
Under normal conditions that will be the case but there are different programs acting on the same table with one having to be a bit and the other string.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply