I have a table with the following columns:
ID INTEGEDR,
Name VARCHAR(32),
Surname VARCHAR(32),
GroupID INTEGER,
SubGroupOneID INTEGER,
SubGroupTwoID INTEGER
How can I create a rule/default/check which update SubGroupOneID &
SubGroupTwoID columns when GroupID for example is equal 15 on
MSSQL2000.
It is imposible to make changes on client, so I need to check
inserted/updated value of GroupID column and automaticly update
SubGroupOneID & SubGroupTwoID columns.
Sincerely,
Rustam BogubaevYou haven't explained what value(s) you want the subgroup columns updated
to.
If the two subgroup columns are solely determined by the the Groupid then
the answer is not to put those columns in the table at all because to do so
destroys normalisation in your schema. Put the subgroups in a separate,
related Groups table.
Rules and Check constraints don't actually change data - they just validate
it. Use a trigger update dependent columns when data is inserted or updated.
See CREATE TRIGGER in Books Online for details.
--
David Portas
----
Please reply only to the newsgroup
--|||You can do this in a trigger, assuming the primary key value is never
changed. For example:
CREATE TABLE MyTable
(
ID int
CONSTRAINT PK_MyTable PRIMARY KEY,
Name varchar(32),
Surname varchar(32),
GroupID int,
SubGroupOneID int,
SubGroupTwoID int
)
GO
CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE t
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM MyTable t
JOIN inserted i ON t.ID = i.ID
WHERE i.GroupID = 15
GO
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
'surname', 1)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
'surname', 2)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
'surname', 15)
SELECT * FROM MyTable
UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
SELECT * FROM MyTable
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
news:20046852.0401202332.386aa33c@.posting.google.c om...
> Hi,
> I have a table with the following columns:
> ID INTEGEDR,
> Name VARCHAR(32),
> Surname VARCHAR(32),
> GroupID INTEGER,
> SubGroupOneID INTEGER,
> SubGroupTwoID INTEGER
> How can I create a rule/default/check which update SubGroupOneID &
> SubGroupTwoID columns when GroupID for example is equal 15 on
> MSSQL2000.
> It is imposible to make changes on client, so I need to check
> inserted/updated value of GroupID column and automaticly update
> SubGroupOneID & SubGroupTwoID columns.
> Sincerely,
> Rustam Bogubaev|||Hi dan
This problem was one I am grappling with, and despite its apparent
simplicity, is not touched on in any simple way in SQL books online, or
other 3rd party books, or rarely in Deja archives.
Could I impose on you a little more to elaborate on a couple of points in
this eample please ?
> For example:
> CREATE TABLE MyTable
> (
> ID int
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Name varchar(32),
> Surname varchar(32),
> GroupID int,
> SubGroupOneID int,
> SubGroupTwoID int
> )
> GO
Yup, I can handle that, lets make a table
> CREATE TRIGGER TR_MyTable
> ON MyTable FOR INSERT, UPDATE
> AS
> UPDATE t
> SET
> SubGroupOneID = 1,
> SubGroupTwoID = 1
> FROM MyTable t
> JOIN inserted i ON t.ID = i.ID
> WHERE i.GroupID = 15
> GO
Making a trigger i can handle, but ..
Bits that puzzle me
UPDATE t, can you explain the reason for and the use of 't' ?
Is that a temporary table where data is stored in the process?
the MyTable t bit, assuming t is a table, should that be MyTable, t -
joining two different tables ?
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> 'surname', 1)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> 'surname', 2)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> 'surname', 15)
> SELECT * FROM MyTable
> UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> SELECT * FROM MyTable
> GO
I presume this next bit is an alternative method, where we insert 3 records,
and then do a bulk update to make
the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand the
"GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
online, as it rarely gives me any usefull results from my inquiries :-)
The other bit that puzzles me is why the "SELECT * FROM MyTable" is needed.
Does the UPDATE row not process all records automatically when the GO is
encountered?
Many thanks for any help you can provide.
> "Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
> news:20046852.0401202332.386aa33c@.posting.google.c om...
> > Hi,
> > I have a table with the following columns:
> > ID INTEGEDR,
> > Name VARCHAR(32),
> > Surname VARCHAR(32),
> > GroupID INTEGER,
> > SubGroupOneID INTEGER,
> > SubGroupTwoID INTEGER
> > How can I create a rule/default/check which update SubGroupOneID &
> > SubGroupTwoID columns when GroupID for example is equal 15 on
> > MSSQL2000.
> > It is imposible to make changes on client, so I need to check
> > inserted/updated value of GroupID column and automaticly update
> > SubGroupOneID & SubGroupTwoID columns.
> > Sincerely,
> > Rustam Bogubaev|||> > CREATE TRIGGER TR_MyTable
> > ON MyTable FOR INSERT, UPDATE
> > AS
> > UPDATE t
> > SET
> > SubGroupOneID = 1,
> > SubGroupTwoID = 1
> > FROM MyTable t
> > JOIN inserted i ON t.ID = i.ID
> > WHERE i.GroupID = 15
> > GO
> Making a trigger i can handle, but ..
> Bits that puzzle me
> UPDATE t, can you explain the reason for and the use of 't' ?
> Is that a temporary table where data is stored in the process?
> the MyTable t bit, assuming t is a table, should that be MyTable, t -
> joining two different tables ?
The 't' is simply an alias declared for MyTable so that I didn't need to
specify the full table name when qualifying column names. The following is
functionally identical. Both examples join MyTable with the inserted table
in order to identify newly inserted or updated rows.
CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE MyTable
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM inserted
WHERE MyTable.ID = inserted.ID AND
inserted.GroupID = 15
GO
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> > 'surname', 1)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> > 'surname', 2)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> > 'surname', 15)
> > SELECT * FROM MyTable
> > UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> > SELECT * FROM MyTable
> > GO
> I presume this next bit is an alternative method, where we insert 3
records,
> and then do a bulk update to make
> the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand
the
> "GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
> online, as it rarely gives me any usefull results from my inquiries :-)
'GroupID IN(1, 2)' is equivalent to 'GroupID = 1 OR GroupID = 2'. You can
find details of 'IN' in the SQL 2000 Books Online. I was able to find the
BOL topic by clicking the index tab, typing 'IN' and double-clicking on the
'IN' keyword in the list. I then selected the 'IN' title from the topic
list.
> The other bit that puzzles me is why the "SELECT * FROM MyTable" is
needed.
The SELECT statements before and after the UPDATE are to display the data
before and after the UPDATE. These are only for illustration.
> Does the UPDATE row not process all records automatically when the GO is
> encountered?
GO is a batch separator. Tools like Query Analyzer execute the preceding
batch of SQL statements when a GO is encountered in the script. The insert,
update and select statements are executed sequentially as part of the same
batch.
--
Hope this helps.
Dan Guzman
SQL Server MVPsql
No comments:
Post a Comment