用触发器实现的 插入 更新 删除 子表也变化
CREATE TABLE [dbo].[tablex] (
[idx] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NULL ,
[productName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
![](/Images/OutliningIndicators/None.gif)
CREATE TABLE [dbo].[tabley] (
[idy] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NULL ,
[productname] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
CREATE TRIGGER triDelete ON [dbo].[tablex]
FOR delete
AS
![](/Images/OutliningIndicators/None.gif)
begin
declare @aa varchar(200)
set @aa=(select productid from deleted)
if @@rowcount>0
delete tabley where productid=@aa
end
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
CREATE trigger tritmp on tablex for insert
as
insert into tabley(ProductID) select i.ProductId from inserted as i where i.ProductId>100
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
CREATE TRIGGER triUpdate ON [dbo].[tablex]
FOR UPDATE
AS
IF UPDATE(productname)
begin
declare @aa varchar(200)
set @aa=(select productid from INSERTED)
declare @bb varchar(200)
set @bb=(select productname from INSERTED)
if (@@rowcount>0)
update tabley set productname=@bb where productid=@aa
end
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
insert tablex values(300,'东方')
![](/Images/OutliningIndicators/None.gif)
update tablex set productname='大海' where productid=300
select * from tablex
![](/Images/OutliningIndicators/None.gif)
select * from tabley
![](/Images/OutliningIndicators/None.gif)
delete from tablex where productid=300
select * from tablex
![](/Images/OutliningIndicators/None.gif)
select * from tabley
其实删除时也可以用外键
删除
CREATE TABLE [dbo].[TABLE1] (
[UserId] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
CREATE TABLE [dbo].[TABLE2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Userid] [int] NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
ALTER TABLE [dbo].[TABLE2] ADD
CONSTRAINT [FK_TABLE2_TABLE1] FOREIGN KEY
(
[Userid]
) REFERENCES [dbo].[TABLE1] (
[UserId]
) ON DELETE CASCADE
GO
![](/Images/OutliningIndicators/None.gif)
select * from table1
![](/Images/OutliningIndicators/None.gif)
select * from table2
![](/Images/OutliningIndicators/None.gif)
insert table1 values('def')
insert table2 values (5,'def')
select * from table1
![](/Images/OutliningIndicators/None.gif)
select * from table2
![](/Images/OutliningIndicators/None.gif)
delete from table1 where userid=5