Hi Celeste,
Here's the sql server 2k online help topic for Cascading referential
integrity constraints. This is probably what you need. You can get msde
(free sql server) from www.microsoft.com/sql by:
1. download sql2k eval copy
2. extract the zipped package
3. navigate the directory hierarchy and find the msde folder.
4. Install msde from the msde folder (think you run setup.bat).
5. Now start the eval copy install and install only the sql server client
tools.
I doubt sql server 7.0 has these cascading deletes. SQL Server 2k is a much
better product.
Cheers,
Rob
Cascading Referential Integrity Constraints
Cascading referential integrity constraints allow you to define the actions
Microsoft� SQL Server� 2000 takes when a user attempts to delete or update a
key to which existing foreign keys point.
The REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements
support ON DELETE and ON UPDATE clauses:
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
NO ACTION is the default if ON DELETE or ON UPDATE is not specified. NO
ACTION specifies the same behavior that occurs in earlier versions of SQL
Server.
ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced
by foreign keys in existing rows in other tables, an error is raised and the
DELETE is rolled back.
ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose
key is referenced by foreign keys in existing rows in other tables, an error
is raised and the UPDATE is rolled back.
CASCADE allows deletions or updates of key values to cascade through the
tables defined to have foreign key relationships that can be traced back to
the table on which the modification is performed. CASCADE cannot be
specified for any foreign keys or primary keys that have a timestamp column.
ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced
by foreign keys in existing rows in other tables, all rows containing those
foreign keys are also deleted. If cascading referential actions have also
been defined on the target tables, the specified cascading actions are also
taken for the rows deleted from those tables.
ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where
the key value is referenced by foreign keys in existing rows in other
tables, all of the foreign key values are also updated to the new value
specified for the key. If cascading referential actions have also been
defined on the target tables, the specified cascading actions are also taken
for the key values updated in those tables.
Examples of cascading referential actions can be based on the
FK_Products_Suppliers constraint on the Products table in Northwind. This
constraint establishes a foreign key relationship from the SupplierID column
in the Products table to the SupplierID primary key column in the Suppliers
table. If ON DELETE CASCADE is specified for the constraint, deleting the
row in Suppliers where SupplierID equals 1 also deletes the three rows in
Products where SupplierID equals 1. If ON UPDATE CASCADE is specified for
the constraint, updating the SupplierID value in the Suppliers table from 1
through 55 also updates the SupplierID values in the three rows in Products
whose SupplierID values currently equal 1.
Cascading actions cannot be specified for a table that has an INSTEAD OF
trigger. After a cascading action has been defined for a table, an INSTEAD
OF trigger cannot be added to it.
Multiple Cascading Actions
Individual DELETE or UPDATE statements can start a series of cascading
referential actions. For example, a database contains three tables, TableA,
TableB, and TableC. A foreign key in TableB is defined with ON DELETE
CASCADE against the primary key in TableA. A foreign key in TableC is
defined with ON DELETE CASCADE against the primary key in TableB. If a
DELETE statement deletes rows in TableA, the operation also deletes any rows
in TableB that have foreign keys matching the deleted primary keys in
TableA, and then deletes any rows in TableC that have foreign keys that
match the deleted primary keys in TableB.
The series of cascading referential actions triggered by a single DELETE or
UPDATE must form a tree containing no circular references. No table can
appear more than once in the list of all cascading referential actions that
result from the DELETE or UPDATE. The tree of cascading referential actions
must not have more than one path to any given table. Any branch of the tree
is terminated when it encounters a table for which NO ACTION has been
specified or is the default.
Triggers and Cascading Referential Actions
Cascading referential actions fire AFTER triggers in this sequence:
All of the cascading referential actions directly caused by the original
DELETE or UPDATE are performed first.
When the original cascading referential actions have completed, the AFTER
triggers on the original table are fired, regardless of whether any rows
were updated.
AFTER triggers on tables in the chain of cascaded referential actions are
then fired, but only if one or more rows in the table have been updated or
deleted.
If any errors are generated by any of the original set of cascading
referential actions, an error is raised, no AFTER triggers are fired, and
the DELETE or UPDATE is rolled back.
An AFTER trigger can execute a DELETE or UPDATE statement that starts
another chain of cascading referential actions. Each secondary chain of
referential actions is treated independently. These secondary chains of
referential actions behave like the primary chain. All of the secondary
referential actions are completed before any secondary triggers are fired.
Within each independent unit, there is no defined order in which the
cascading referential actions are executed and the affected triggers are
fired.
A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause
that specifies a cascading action. An AFTER trigger on a table targeted by a
cascading action, however, can execute an INSERT, UPDATE, or DELETE
statement on another table or view that fires an INSTEAD OF trigger defined
on that object.
Cascading Referential Constraints Catalog Information
The following catalog information is available about cascading referential
constraints.
The Transact-SQL OBJECTPROPERTY function supports these new values for the
property parameter.
Value Object Description
CnstIsDeleteCascade Constraint FOREIGN KEY constraint defined with ON DELETE
CASCADE
CnstIsUpdateCascade Constraint FOREIGN KEY constraint defined with ON UPDATE
CASCADE
The REFERENTIAL_CONSTRAINTS information schema view returns CASCADE in the
UDPATE_RULE or DELETE_RULE column when either ON UPDATE CASCADE or ON DELETE
CASCADE is specified. NO ACTION is returned when either ON UPDATE NO ACTION
or ON DELETE NO ACTION is specified, or if ON UPDATE or ON DELETE is not
specified at all.
The UPDATE_RULE and DELETE_RULE columns returned by sp_fkeys and
sp_foreignkeys are set to 1 when CASCADE is specified, and return 0 when NO
ACTION is specified or is the default.
When a foreign key is specified as the object of sp_help, the output result
set contains these new columns.
Column name Data type Description
delete_action nvarchar(9) Indicates whether the delete action is CASCADE, NO
ACTION, or N/A (not applicable).
update_action nvarchar(9) Indicates whether the update action is CASCADE, NO
ACTION, or N/A (not applicable).
�1988-2000 Microsoft Corporation. All Rights Reserved.
-----Original Message-----
From: Haseltine, Celeste [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 08, 2001 3:52 PM
To: [EMAIL PROTECTED]
Subject: JSP and SQLServer
Has anyone else on this board been unfortunate enough to be tasked with
developing a JSP/Java product against an existing SQLServer 7.0 database on
the back end? As a developer, I am familiar with using Oracle and
SQLAnywhere on the back end with Java/JSP, both of which have the concept of
"cascade deletes". But I have been fortunate enough NOT to have worked with
SQLServer, until today. MS SQLServer 7.0 does not utilize the concept of
"cascade deletes", and uses triggers instead to "cascade delete" among
related tables within a schema. If anyone else has been in the same
position as I now find myself in, can you direct me to some SQLServer web
sites/resources that explain how to write triggers that can accept a
parameter in SQLServer? The MS web site has not been of much use to me
today, including SQLServer Books Online, so I am hoping someone else out
there may have run across some better resources/discussion groups regarding
this subject.
Thanks in advance!!!!
Celeste
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff
JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST
DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
==========================================================================To
unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
