当我写SQL语句:delete from testaaa where Data=001时为什么两表里的记录全部被删了,Data为:002的记录怎么同时也被删除了。请你们帮忙帮我看看......testaaa表有以下记录:
Data Time1
001 2008-10-20
002 2008-10-21 message表有以下记录:
Data Time1
001 2008-10-20
001 2008-10-21
002 2008-10-21 我写了以下的触发器:
CREATE TRIGGER aa ON testaaa
FOR DELETE
AS
delete from message
where Data = message.Data
delete from testaaa
where Data = testaaa.Data
Data Time1
001 2008-10-20
002 2008-10-21 message表有以下记录:
Data Time1
001 2008-10-20
001 2008-10-21
002 2008-10-21 我写了以下的触发器:
CREATE TRIGGER aa ON testaaa
FOR DELETE
AS
delete from message
where Data = message.Data
delete from testaaa
where Data = testaaa.Data
CREATE TRIGGER aa ON testaaa
FOR DELETE
AS
delete message from deleted d
where message.Data=d.data
FOR DELETE
AS
DELETE message FROM message A,DELETED B WHERE A.Data=B.Data
go
create table testaaa(Data varchar(10), Time1 datetime)
insert testaaa select '001' , '2008-10-20'
insert testaaa select '002', '2008-10-21'
if object_id('message')is not null drop table message
go
create table message(Data varchar(10), Time1 datetime)
insert message select '001' , '2008-10-20'
insert message select '001' , '2008-10-21'
insert message select '002', '2008-10-21'
if object_id('aa')is not null drop trigger aa
go
CREATE TRIGGER aa ON testaaa
FOR DELETE
AS
delete message from deleted d
where message.Data=d.data
go
delete from testaaa where Data='001'
select * from message
/*Data Time1
---------- ------------------------------------------------------
002 2008-10-21 00:00:00.000*/
AS
delete from message where Data = (select data from deleted)
go
drop table testaaa
Go
Create table testaaa([Data] nvarchar(3),[Time1] Datetime)
Insert testaaa
select N'001','2008-10-20' union all
select N'002','2008-10-21'
if not object_id('message') is null
drop table message
Go
Create table message([Data] nvarchar(3),[Time1] Datetime)
Insert message
select N'001','2008-10-20' union all
select N'001','2008-10-21' union all
select N'002','2008-10-21'
GOCREATE TRIGGER TR_testaaa ON testaaa
FOR DELETE
AS
DELETE message FROM message A,DELETED B WHERE A.Data=B.DataGO
DELETE testaaa WHERE Data='001'Select * from testaaa
Select * from message/*Data Time1
---- ------------------------------------------------------
002 2008-10-21 00:00:00.000(所影响的行数为 1 行)Data Time1
---- ------------------------------------------------------
002 2008-10-21 00:00:00.000(所影响的行数为 1 行)
*/
应该是:
DELETE MESSAGE WHERE DATA IN (SELECT DATA FROM DELETED)
或用级联删除