sql server programming: Hi
I'm new to SQL Server and am trying to learn some of the fundamentals. I have a table, "Vendors", with the usual fields like Name, Address, etc. Two of the fields have some relation to each other. One field is "Discontinued", the other is "Preferred". If a Vendor is "Preferred", then I want to insure that "Discontinued" is set to FALSE (or "0" in the SQL Server bit data type). If a user sets "Discontinued" to TRUE, (1), I want to make sure that "Preferred" is set to FALSE. These conditions should be checked whether the record is an INSERT or an UPDATE

I'm not sure if using a constraint is better in this case, but I'd still like to see if you can provide me an example using a trigger just so I can learn about them. Most of the examples I find in books and elsewhere don't show much more than issuing a PRINT statement if a field is updated to a certain value

Thanks in advance

Paul,

You will need to do this in a trigger because you will have to check the =
state of each field at update or insert because to do this in a =
constraint you would have a circular reference. If you supplied a table =
schema I could actually do this for you, this is my best guess. Also, =
within your front-end you will have to ensure that someone cannot flip =
both flags to Preferred or Discontinued at the same time or have one =
have superiority over the other because again you hit a circular =
reference. I have done my best to build a trigger that would accomodate =
your post and tested as best I could without your DDL. For next time, =
you should post DDL with your questions it helps us better understand =
your problems. There is one intentional limitation of this trigger I =
have created. If a flag was already flipped to 1 and you flip the other =
flag to one then I am overwriting the previous flag with the new change. =
You can modify this behavior by changing the where clause in the test =
for flipped flags that begins before the update of the vendor table. =
Also, you will need to change the Vnumber column to your primary key of =
your table.

HTH,
Grant

if exists (select * from dbo.sysobjects where id =3D =
object_id(N'[dbo].[TIU_Vendor_SetPreferredDiscontinuedFlag]') and =
OBJECTPROPERTY(id, N'IsTrigger') =3D 1)
drop trigger [dbo].[TIU_Vendor_SetPreferredDiscontinuedFlag]
GO

CREATE TRIGGER TIU_Vendor_SetPreferredDiscontinuedFlag
ON dbo.Vendor
FOR INSERT, UPDATE
AS
/*********************************HDR************************************=
*
*********GENERAL*********
OBJECT NAME: TIU_Vendor_SetPreferredDiscontinuedFlag
OBJECT TYPE: Trigger
SUBCATEGORY: Data Integrity
CREATED BY: GRANT CASE=20
DATE: 04/14/2004
MODIFIED BY: GRANT CASE
DATE: 04/14/2004
DESCRIPTION:
This trigger updates the vendor table and flips the flag preferred=20
and discontinued by the changes made within the application.
Example:
If Preferred Flag is flipped to 1 then Discontinued will be flipped to =
0
If Discontinued Flag is flipped to 1 then Preferred will be flipped to =
0
All new modifications of the row overwrite previous changes such that if =
a
row's Preferred column were set to 1 and the new record sets the =
Discontinued =3D 1
then the Preferred column will have its value set back to 0.



*********VARIABLES*********
NAME TYPE LOCAL/PASSED DEFAULT=20
DESCRIPTION =20
---------------------------------------------------------------


*********DEPENDENCIES*********
TABLES:


OBJECTS:


*********TEMP TABLES*********
NAME DESCRIPTION/USE


*********TRIGGER SPECIFIC*********
SOURCE TABLE: =20
DESTINATION TABLE:=20
CASCADED TRG: =20
CASCADED TRG TABLE: =20


*********REPORT SPECIFIC*********
REPORTS USING STORED PROCEDURE:


*********OUTPUT*********


*********MODIFICATION LOG*********
DATE INITIALS MODIFICATION
04/14/2004 GSC Created =20

*********HEADER CONVENTIONS*********
DO NOT GO PAST 75 CHARACTERS BEFORE GOING TO THE NEXT LINE
TO DEBUG, REPLACE ALL "-- DEBUG"
*********************************HDR*************************************=
/


-------------------------------------------------------------------------=
-
-- LOCAL VARIABLE DECLARATION SECTION
-------------------------------------------------------------------------=
-
DECLARE @ActionType AS VARCHAR (10)

-------------------------------------------------------------------------=
-
-- LOCAL VARIABLE SET SECTION
-------------------------------------------------------------------------=
-
--IF YOU DO NOT SET THIS, ACTIVE SERVER PAGES WILL HAVE PROBLEMS
SET NOCOUNT ON
SET @ActionType =3D CASE=20
WHEN NOT EXISTS (SELECT 1 FROM DELETED) AND NOT EXISTS (SELECT 1 FROM =
INSERTED) THEN 'None'
WHEN EXISTS (SELECT 1 FROM DELETED) AND EXISTS (SELECT 1 FROM INSERTED) =
THEN 'Update'
WHEN NOT EXISTS (SELECT 1 FROM DELETED) AND EXISTS (SELECT 1 FROM =
INSERTED) THEN 'Insert'
WHEN EXISTS (SELECT 1 FROM DELETED) AND NOT EXISTS (SELECT 1 FROM =
INSERTED) THEN 'Delete'
END
-------------------------------------------------------------------------=
-
-- TEMP TABLE CREATION SECTION
-------------------------------------------------------------------------=
-

-------------------------------------------------------------------------=
-
-- CODE SECTION
-------------------------------------------------------------------------=
-
IF @ActionType =3D 'Update'
BEGIN

=
/************************************************************************=
*
Test to ensure that the records being modified are not both being =
flipped
to ON for the first time. If a record is already flipped to 1 and the=20
other flag is flipped to 1 then the previously flipped flag will be=20
flipped to 0.
=
*************************************************************************=
/
IF EXISTS (SELECT 1 FROM Inserted=20
INNER JOIN Deleted ON Inserted.VNumber =3D Deleted.VNumber=20
WHERE Inserted.Discontinued <> Deleted.Discontinued
AND Inserted.Preferred <> Deleted.Preferred
AND Inserted.Preferred =3D 1 AND Inserted.Discontinued =3D 1)
BEGIN
RAISERROR ('Discontinued and Preferred Flag may not be set to true at =
the same time',16, 1)
ROLLBACK TRANSACTION
RETURN
END

UPDATE Vendor
SET Vendor.Preferred =3D CASE
WHEN Inserted.Discontinued <> Deleted.Discontinued AND =
Inserted.Discontinued =3D 1 THEN 0
ELSE Vendor.Preferred
END,
Vendor.Discontinued =3D CASE
WHEN Inserted.Preferred <> Deleted.Preferred AND Inserted.Preferred =
=3D 1 THEN 0
ELSE Vendor.Discontinued
END
FROM Vendor
INNER JOIN Inserted ON Vendor.VNumber =3D Inserted.VNumber
INNER JOIN Deleted ON Inserted.VNumber =3D Deleted.VNumber
WHERE Inserted.Discontinued <> Deleted.Discontinued
OR Inserted.Preferred <> Deleted.Preferred
=20
IF @@ERROR <> 0 ROLLBACK TRANSACTION
END

IF @ActionType =3D 'Insert'
BEGIN
=
/************************************************************************=
*
Test to ensure that the records being entered do not have both a =
preferred
and discontinued flag set to 1.
=
*************************************************************************=
/
IF EXISTS (SELECT 1 FROM Inserted WHERE Inserted.Preferred =3D 1 AND =
Inserted.Discontinued =3D 1)
BEGIN
RAISERROR ('Discontinued and Preferred Flag may not be set to true at =
the same time',16, 1)
ROLLBACK TRANSACTION
RETURN
END
=20
=20
UPDATE Vendor
SET Vendor.Preferred =3D CASE
WHEN Inserted.Discontinued =3D 1 THEN 0
ELSE Vendor.Preferred
END,
Vendor.Discontinued =3D CASE
WHEN Inserted.Preferred =3D 1 THEN 0
ELSE Vendor.Discontinued
END
FROM Vendor
INNER JOIN Inserted ON Vendor.VNumber =3D Inserted.VNumber
=20
IF @@ERROR <> 0 ROLLBACK TRANSACTION
END

Hugo

Thanks so much for the info! Unfortunately, I tried this approach right off the bat, but our purchasing department shot down the idea due to several reasons which I concurred with. Some of it has to do with legacy data. We're in somewhat of a transitional period so perhaps in the end, we'll be able to simplify things but for the moment, I'm limited to dealing with the 2 fields

Regards

Grant

I'm amazed at the detail you've provided! I'm not sure it's what your looking for, but below is the text from a SQL script file that defines the table in question (tblVendor)

Notes: The front end of this system is a Microsoft Access XP database (MDB format, not an Access Project). Until Jan. 1 of this year, the back end was also Access, but our company took over a competitor across the country and we had to immediately upsize to SQL Server. We've been going thru the teething pains of getting the MDB to work cleanly w/ SQL. With respect to the posted issue, I'll be putting some logic into the front end to control the values, as per your suggestion

Here's the script that was generated for the table (I couldn't find a way to attach it as a file)

/***** START SCRIPT HERE ***********

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMatVend_FK01]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1
ALTER TABLE [dbo].[tblMatVend] DROP CONSTRAINT tblMatVend_FK0
G

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPurReqItems_FK02]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1
ALTER TABLE [dbo].[tblPurReqItems] DROP CONSTRAINT tblPurReqItems_FK0
G

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRANum_FK01]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1
ALTER TABLE [dbo].[tblRANum] DROP CONSTRAINT tblRANum_FK0
G

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblVend_Eq_FK01]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1
ALTER TABLE [dbo].[tblVend_Eq] DROP CONSTRAINT tblVend_Eq_FK0
G

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblVendContact_FK00]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1
ALTER TABLE [dbo].[tblVendContact] DROP CONSTRAINT tblVendContact_FK0
G

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblVendor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
drop table [dbo].[tblVendor
G

CREATE TABLE [dbo].[tblVendor]
[vend_id] [int] IDENTITY (1, 1) NOT NULL
[vend_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
[v_address1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_address2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_address3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_city] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_state] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_zip] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_country] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_phone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_phone2] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_fax] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_fax2] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_acct#] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_terms] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_discount] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_fob] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_discon] [bit] NULL
[v_dealer] [bit] NULL
[v_prefer] [bit] NULL
[v_afa] [bit] NULL
[v_website] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[v_notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[upsize_ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY
G

/********** END SCRIPT HERE *************

Thank you very much for your help. I'm still reviewing your notes to get a good understanding as to what's going on. Although I've been coding MS Access and VB databases for years, I've only just now moved into SQL Server, so I'm back to being a novice

Thanks again

[quoted text, click to view]

Hi Paul,

Maybe you could also consider collapsing these two columns into one,
holding the vendor's status:

CREATE TABLE Vendors
(....
....
Status varchar(12) not null default('normal')
check (Status in ('normal', 'preferred', 'discontinued')))

Best, Hugo
--

[quoted text, click to view]
check the state of each field at update or insert because to do this in
a constraint you would have a circular reference.
<<<<<

You could implement this as a constraint and the constraint would
perfrom much better than the trigger.

create table #junk (
JunkKey int primary key,
Preferred bit not null,
Discontinued bit not Null,

constraint CustStatus check (
cast(Preferred as smallint) +
cast(Discontinued as smallint) < 2
)
)
go

insert #junk values (1, 0, 0)
go

insert #junk values (2, 0, 1)
go

insert #junk values (3, 1, 0)
go

insert #junk values (4, 1, 1) -- Illegal
go

update #junk -- Illegal
set Discontinued = 1
where JunkKey = 3
go

select *
from #Junk


HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]

Paul,

In that case, see if Grant's suggestion does what you need. I didn't
go into all details, but on first glance it looks goods.

Best, Hugo
--

There was one thing I saw Paul and the reason we post DDL :). You can have
a NULL in these fields so therefore I changed the trigger around including
adding the changes for your table schema to compensate for this. Enjoy!

HTH,
Grant

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TIU_Vendor_SetPreferredDiscontinuedFlag]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TIU_Vendor_SetPreferredDiscontinuedFlag]
GO

CREATE TRIGGER TIU_Vendor_SetPreferredDiscontinuedFlag
ON dbo.tblVendor
FOR INSERT, UPDATE
AS
/*********************************HDR*************************************
*********GENERAL*********
OBJECT NAME: TIU_Vendor_SetPreferredDiscontinuedFlag
OBJECT TYPE: Trigger
SUBCATEGORY: Data Integrity
CREATED BY: GRANT CASE
DATE: 04/14/2004
MODIFIED BY: GRANT CASE
DATE: 04/14/2004
DESCRIPTION:
This trigger updates the tblVendor table and flips the flag v_prefer
and v_discon by the changes made within the application.
Example:
If v_prefer Flag is flipped to 1 then v_discon will be flipped to 0
If v_discon Flag is flipped to 1 then v_prefer will be flipped to 0
All new modifications of the row overwrite previous changes such that if a
row's v_prefer column were set to 1 and the new record sets the v_discon = 1
then the v_prefer column will have its value set back to 0.



*********VARIABLES*********
NAME TYPE LOCAL/PASSED DEFAULT
DESCRIPTION
---------------------------------------------------------------


*********DEPENDENCIES*********
TABLES:


OBJECTS:


*********TEMP TABLES*********
NAME DESCRIPTION/USE


*********TRIGGER SPECIFIC*********
SOURCE TABLE:
DESTINATION TABLE:
CASCADED TRG:
CASCADED TRG TABLE:


*********REPORT SPECIFIC*********
REPORTS USING STORED PROCEDURE:


*********OUTPUT*********


*********MODIFICATION LOG*********
DATE INITIALS MODIFICATION
04/14/2004 GSC Created

*********HEADER CONVENTIONS*********
DO NOT GO PAST 75 CHARACTERS BEFORE GOING TO THE NEXT LINE
TO DEBUG, REPLACE ALL "-- DEBUG"
*********************************HDR*************************************/


--------------------------------------------------------------------------
-- LOCAL VARIABLE DECLARATION SECTION
--------------------------------------------------------------------------
DECLARE @ActionType AS VARCHAR (10)

--------------------------------------------------------------------------
-- LOCAL VARIABLE SET SECTION
--------------------------------------------------------------------------
--IF YOU DO NOT SET THIS, ACTIVE SERVER PAGES WILL HAVE PROBLEMS
SET NOCOUNT ON
SET @ActionType = CASE
WHEN NOT EXISTS (SELECT 1 FROM DELETED) AND NOT EXISTS (SELECT 1 FROM
INSERTED) THEN 'None'
WHEN EXISTS (SELECT 1 FROM DELETED) AND EXISTS (SELECT 1 FROM INSERTED)
THEN 'Update'
WHEN NOT EXISTS (SELECT 1 FROM DELETED) AND EXISTS (SELECT 1 FROM INSERTED)
THEN 'Insert'
WHEN EXISTS (SELECT 1 FROM DELETED) AND NOT EXISTS (SELECT 1 FROM INSERTED)
THEN 'Delete'
END
--------------------------------------------------------------------------
-- TEMP TABLE CREATION SECTION
--------------------------------------------------------------------------

--------------------------------------------------------------------------
-- CODE SECTION
--------------------------------------------------------------------------
IF @ActionType = 'Update'
BEGIN

/*************************************************************************
Test to ensure that the records being modified are not both being flipped
to ON for the first time. If a record is already flipped to 1 and the
other flag is flipped to 1 then the previously flipped flag will be
flipped to 0.
*************************************************************************/
IF EXISTS (SELECT 1 FROM Inserted
INNER JOIN Deleted ON Inserted.vend_id = Deleted.vend_id
WHERE ((Inserted.v_discon <> Deleted.v_discon) OR (Inserted.v_discon = 1
AND Deleted.v_discon IS NULL))
AND ((Inserted.v_prefer <> Deleted.v_prefer) OR (Inserted.v_prefer = 1 AND
Deleted.v_prefer IS NULL))
AND Inserted.v_prefer = 1 AND Inserted.v_discon = 1)

BEGIN
RAISERROR ('v_discon and v_prefer Flag may not be set to true at the same
time',16, 1)
ROLLBACK TRANSACTION
RETURN
END


UPDATE tblVendor
SET tblVendor.v_prefer = CASE
WHEN (Deleted.v_discon IS NULL OR Deleted.v_discon = 0) AND
Inserted.v_discon = 1 THEN 0
ELSE tblVendor.v_prefer
END,
tblVendor.v_discon = CASE
WHEN (Deleted.v_prefer IS NULL OR Deleted.v_prefer = 0) AND
Inserted.v_prefer = 1 THEN 0
ELSE tblVendor.v_discon
END
FROM tblVendor
INNER JOIN Inserted ON tblVendor.vend_id = Inserted.vend_id
INNER JOIN Deleted ON Inserted.vend_id = Deleted.vend_id
WHERE ((Deleted.v_discon = 0 OR Deleted.v_discon IS NULL) AND
Inserted.v_discon = 1)
OR ((Deleted.v_prefer = 0 OR Deleted.v_prefer IS NULL) AND
Inserted.v_prefer = 1 )

IF @@ERROR <> 0 ROLLBACK TRANSACTION
END

IF @ActionType = 'Insert'
BEGIN
/*************************************************************************
Test to ensure that the records being entered do not have both a v_prefer
and v_discon flag set to 1.
*************************************************************************/
IF EXISTS (SELECT 1 FROM Inserted WHERE Inserted.v_prefer = 1 AND
Inserted.v_discon = 1)
BEGIN
RAISERROR ('v_discon and v_prefer Flag may not be set to true at the same
time',16, 1)
ROLLBACK TRANSACTION
RETURN
END


UPDATE tblVendor
SET tblVendor.v_prefer = CASE
WHEN Inserted.v_discon = 1 THEN 0
ELSE tblVendor.v_prefer
END,
tblVendor.v_discon = CASE
WHEN Inserted.v_prefer = 1 THEN 0
ELSE tblVendor.v_discon
END
FROM tblVendor
INNER JOIN Inserted ON tblVendor.vend_id = Inserted.vend_id

IF @@ERROR <> 0 ROLLBACK TRANSACTION
END



--------------------------------------------------------------------------
-- TESTING SECTION
--------------------------------------------------------------------------

--------------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------------

GO



[quoted text, click to view]
looking for, but below is the text from a SQL script file that defines the
table in question (tblVendor).
[quoted text, click to view]
format, not an Access Project). Until Jan. 1 of this year, the back end was
also Access, but our company took over a competitor across the country and
we had to immediately upsize to SQL Server. We've been going thru the