22,207
社区成员
发帖
与我相关
我的任务
分享关于主从表触发器的问题！
我见了一个主从表，在从表中添加了触发器，将主从表的关系设置为级连（cascade），子表触发器中使用到了主表中的字段，当删除主表时，子表的触发器执行了，但是，我现在取不到主表中的值（由于主表中的记录已经删除了），请问高手，cascade的处理机制，我认为应该是先删除子表再删除主表本身啊！难道是先删除主表吗？
...全文
请发表友善的回复…
发表回复
phenixSolon 2003-10-28
- 打赏
- 举报
ok
Wally_wu 2003-10-25
- 打赏
- 举报
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).
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).
pengdali 2003-10-25
- 打赏
- 举报
删除主表你把触发器建立在主表上不行吗？
phenixSolon 2003-10-25
- 打赏
- 举报
up
phenixSolon 2003-10-24
- 打赏
- 举报
我在从表中使用了一个主表的字段（例如：人员ID），在从表的触发器中，根据该字段在引用其他的表，处理业务数据。
从现在的执行效果上来看是先删除的主表（即将主表的记录删除，放到deleted表中了，我在从表中用print显示的控制，所以有此判断）。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
不知道说明白了吗？
从现在的执行效果上来看是先删除的主表（即将主表的记录删除，放到deleted表中了，我在从表中用print显示的控制，所以有此判断）。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
不知道说明白了吗？
sjw800614 2003-10-24
- 打赏
- 举报
学习先!!!
sjw800614 2003-10-24
- 打赏
- 举报
我不明白你到底想要做什么!!!
可以 详细的说一下吗?>??
可以 详细的说一下吗?>??
txlicenhe 2003-10-24
- 打赏
- 举报
ON DELETE CASCADE
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。
-----------------
我想是先删除主表。
因为删除子表时级联删除并没有起作用，而如果删除子表后，再删除主表，此时对主表来说也没有级联的效果了。
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。
-----------------
我想是先删除主表。
因为删除子表时级联删除并没有起作用，而如果删除子表后，再删除主表，此时对主表来说也没有级联的效果了。
pengdali 2003-10-24
- 打赏
- 举报
尝试使用替代触发器如：
CREATE TRIGGER 名 on 表
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO 表
SELECT name1,name1
FROM inserted
END
CREATE TRIGGER 名 on 表
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO 表
SELECT name1,name1
FROM inserted
END
