Trigger validation issue - SQL Server Forums

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

Trigger validation issue

Author

Topic

asc45
Starting Member

3 Posts

Posted - 2009-07-22 : 16:21:00

hi,

i am new to writting triggers, so i thought someone might shade some light to my following problem:

i am trying to create an Insert/update trigger on a table "stockitem" which has a primary key 'typeid' and 'refernceno' as one of its many columns. My trigger's main context is as follows:

Declare @referenceno varchar(50)
Decalre cursor cr_ref local for
select insert.referenceno from stockitem where stockitem.typeid = insert.typeid

If update(referenceno) BEGIN
OPEN cr_ref
FETCH NEXT FROM cr_ref INTO @referenceno
WHILE @@FETCH_STATUS = 0 BEGIN
if exists(select * from stockitem where referenceno = @referenceno)
BEGIN
RAISERROR(*****VALIDATION FAILED*****,16,1)
END

-- rest of the code

The problem is that the 'if exists' statment always returns 'true' even if the table has no referenceno corresponding the insert.refernceno. After some debugging, i realised that the table seems to add the insert row 'virtually' during the scope of the trigger. Does anyone know how i can do the validation check on the original table by comparing it to the inserted table?

Thanks in advance

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:26:36

how bout just putting a unique contraint on referenceno?

asc45
Starting Member

3 Posts

Posted - 2009-07-22 : 16:31:48

how do i do that? i tried the following query to debug and it returns me '1' even though there is no such referenceno in the table:

if (select count(distinct(typeid)) from stockitem where referenceno = @referenceno) > 0
BEGIN
RAISERROR(*****VALIDATION FAILED*****,16,1)
END

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:41:42

[code]
ALTER TABLE yourTable
ADD Constraint UX_referenceno UNIQUE (referenceno)
[/code]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 16:42:03

then u dont need the trigger at all

asc45
Starting Member

3 Posts

Posted - 2009-07-22 : 16:54:51

you see i can not have unique constraints on 'referenceno' since we have to allow duplicate of 'N/A' or null. Also, the interface needs a raiseerror to show the user that they can not add duplicate on stock items.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-22 : 21:31:25

couldn't see that from prior posts. what happens if you change to select referenceno from stockitem ?

perhaps if u show us your schema it will help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources