I am trying to create a trigger on a table. The idea is
that the trigger will stop duplicates being entered but
will allow null values. I am trying to use the inserted
table but am receiving the following error.
The column prefix 'T2' does not match with a table name
or alias name used in the query.
The table and trigger creation script follows.
Thanks in advance.
========= Table
=========
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[Class]
GO
CREATE TABLE [dbo].[Class] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (60) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Symbol] [T_STD_SYMBOL] NULL ,
[ClassTypeID] [int] NOT NULL ,
[Description] [varchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
========= Trigger
=========
if exists (select 1
from sysobjects
where id = object_id('uqSymbol_class')
and type = 'TR')
drop trigger uqSymbol_class
go
/* Trigger to stop duplicate Symbols being entered in
the Symbol column in the class table. */
create trigger uqSymbol_class on Class for insert, update
as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
if @.numrows = 0
return
/* Check to see if the inserted updated symbol
already exists */
if update(symbol)
begin
-- Check weather the Symbol value is Null.
Ignore if so.
-- if ((select symbol from inserted) != null)
begin -- IF((SELECT COUNT(*) FROM INSERTED WHERE
Symbol IS NOT NULL) > 0)
if (select count(*)
from Class t1, inserted t2
where t1.symbol = t2.symbol) >1 AND
T2.Symbol IS NOT NULL
begin
select @.errno = 30003,
@.errmsg = 'The Symbol you have
entered or just updated already exists.'
goto error
end
end
-- end
return
/* Error handling */
error:
raiserror @.errno @.errmsg
rollback transaction
end
ThanX :-)You are not giving us any code which uses t2 as an alias... please repost
complete code...
also if there are ever multiple triggers, and one of the other triggers does
a select insert update or delete, then @.@.rowcount in this trigger will not
be correct. It is probably safer to count records in inserted and deleted...
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||You missed out the relevant bit of code.
In fact you don't need a trigger to do this. You can use an indexed view to
enforce uniqueness only for non-NULL values:
CREATE VIEW Symbols
WITH SCHEMABINDING
AS
SELECT symbol
FROM dbo.class
WHERE symbol IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclsymbol ON Symbols (symbol)
--
David Portas
--
Please reply only to the newsgroup
--|||Hi Jamie,
Posting messages with attachments usually isn't very useful because most
people do not trust them, won't open them and so don't have enough
information to answer your question. Post everything in plain text instead.
If you are on SQL Server 2000 you don't have to use a trigger to achieve
this, but you can instead create an indexed view to check for duplicates:
CREATE VIEW Class_Symbol_check
AS
SELECT Symbol FROM Class
WHERE Symbol IS NOT NULL
GO
CREATE UNIQUE INDEX ON Class_Symbol_check (Symbol )
GO
Read the topic "Indexed views" in Books online for more information.
The fact that a UNIQUE index can only have one NULL (otherwise you could
just create a unique constraint on the Symbol column in the class table) is
a problem in SQL Server and one I sincerly hope will be addressed in the
next version.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0d4101c360b0$976a42e0$a101280a@.phx.gbl...
> I am trying to create a trigger on a table. The idea is
> that the trigger will stop duplicates being entered but
> will allow null values. I am trying to use the inserted
> table but am receiving the following error.
> The column prefix 'T2' does not match with a table name
> or alias name used in the query.
> The table and trigger creation script follows.
> Thanks in advance.
> =========> Table
> =========> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[Class]
> GO
> CREATE TABLE [dbo].[Class] (
> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (60) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Symbol] [T_STD_SYMBOL] NULL ,
> [ClassTypeID] [int] NOT NULL ,
> [Description] [varchar] (40) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> =========> Trigger
> =========> if exists (select 1
> from sysobjects
> where id = object_id('uqSymbol_class')
> and type = 'TR')
> drop trigger uqSymbol_class
> go
> /* Trigger to stop duplicate Symbols being entered in
> the Symbol column in the class table. */
> create trigger uqSymbol_class on Class for insert, update
> as
> begin
> declare
> @.numrows int,
> @.errno int,
> @.errmsg varchar(255)
> select @.numrows = @.@.rowcount
> if @.numrows = 0
> return
>
> /* Check to see if the inserted updated symbol
> already exists */
> if update(symbol)
> begin
> -- Check weather the Symbol value is Null.
> Ignore if so.
> -- if ((select symbol from inserted) != null)
>
----
--
> ThanX :-)|||> a problem in SQL Server and one I sincerly hope will be addressed in the
> next version.
Full SQL92 constraints would solve this problem and more. I hope Yukon will
support the ANSI-style constraints.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah,
In the all the info I have read and heard about Yukon there is a lot of talk
about CLR support, and only very general remarks about improvements in
T-SQL. I guess the marketing people at Microsoft think that (full ANSI
constraints etc) is "hard core SQL" and too difficult for 95% of the
developers, and they are probably right. Euan Garden (Program manager for
SQL Server) said during a presentation "that people think that Microsoft
will drop support for T-SQL now that there's CLR support, but that's not the
case", so that tells you the level they are targeting. </rant>
Of the new SQL features in Yukon that I know of I like the idea in Yukon
that you can write your own datatypes, I just don't like the idea that you
have to write them in a CLR language instead of being able to declare them
in SQL, although I expect that there will soon be a cottage industry in
datatypes for SQL Server (postcodes, telephone numbers, credit card numbers,
ISBN etc), just like there was with ActiveX controls for VB 6.
But there is little or no information on structural improvement to T-SQL and
further compliance with ANSI standards. Do we get ANSI style constraints?
row constructors? ...?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:#ZqfnQLYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > a problem in SQL Server and one I sincerly hope will be addressed in the
> > next version.
> Full SQL92 constraints would solve this problem and more. I hope Yukon
will
> support the ANSI-style constraints.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>
>|||Thanks for all your help. I have implemented the view as
the trigger.
Jacco, what did you mean by CLR.
Thanks again.
