473,387 Members | 1,641 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

how to create rule which update other columns

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
Jul 20 '05 #1
4 5289
You 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
--
Jul 20 '05 #2
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" <rb*******@bookinturkey.com> wrote in message
news:20**************************@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

Jul 20 '05 #3
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" <rb*******@bookinturkey.com> wrote in message
news:20**************************@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


Jul 20 '05 #4
> >
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 MVP
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: efinney | last post by:
Hi, I'm a newbie to sql server and this may be a really dumb question for some you. I'm trying to find some examples of sql server triggers that will set columns (e.g. the created and modified...
7
by: Wolfgang Kreuzer | last post by:
Hello all, I have two tables - Projects and ProjectStruct Table Projects contains master records of the projects, ProjectStruct allows to define a project herarchie and contains the fields...
0
by: jtocci | last post by:
I'm having a big problem with CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50) records based on a single INSERT to a view. Either I get a 'too...
1
by: sunlight_sg | last post by:
Hello, i am using ADOX + VB .NET to create a Access Database programmatically. I plan to set some properties of the column such primary key. The code is as follows: Dim cat As ADOX.Catalog...
4
by: Jeff Boes | last post by:
(I thought I posted this yesterday from Google Groups, but it doesn't appear to have "taken".) I'm having a problem with a rule designed to log new rows inserted into one table. The base table...
27
by: max | last post by:
Hello, I am a newbye, and I'm trying to write a simple application. I have five tables with three columns; all tables are identical; I need to change some data in the first table and let VB...
5
by: explode | last post by:
I made a procedure Public Sub Novo(ByVal nova1 As String, ByVal nova2 As String) that creates a new oledbDataAdapter with insert update select and delete commads. I also added that commands can...
8
by: Ornette | last post by:
Hello, I have a stored procedure which generates some values in the table. When I use update() how to populate the dataset with theses values ? For the moment I use output parameter but it...
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.