关于sql server 2005 触发器　问题。

hyit2004 2007-08-06 11:27:00

CREATE TABLE MYTABLE_LOG(ID INT, NAME VARCHAR(100))
GO
CREATE TABLE [dbo].[MYTABLE](
[UID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,
[NAME] [varchar](100) NULL,
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[UID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

create TRIGGER [MYTABLE_2sernet] on [MYTABLE]
AFTER INSERT,UPDATE,DELETE
AS
begin
insert into MYTABLE_LOG(ID,[NAME])
select coalesce(ins.ID,del.ID),coalesce(ins.[NAME],del.[NAME])
from inserted ins full join deleted del on ins.UID=del.UID
where coalesce(ins.ID,del.ID)
not in (select distinct ID from MYTABLE_LOG (nolock))
end
go

INSERT INTO MYTABLE
SELECT 1,'A'
union all SELECT 2,'B'
union all SELECT 3,'C'
union all SELECT 4,'D'
union all SELECT 5,'E'
union all SELECT 6,'F'
union all SELECT 1,'A'
union all SELECT 2,'B'
union all SELECT 3,'C'
union all SELECT 4,'D'
union all SELECT 5,'E'
union all SELECT 6,'F'
go
select * from MYTABLE
select * from MYTABLE_LOG order by ID
问题描述：
我本来想MYTABLE_LOG只有６条记录。（重复的数据不存在）
正确结果如下：
ID NAME
1 A
2 B
3 C
4 D
5 E
6 F
而不是
ID NAME
1 A
1 A
2 B
2 B
3 C
3 C
4 D
4 D
5 E
5 E
6 F
6 F

我的sp_configure设置：
name 　　　　　　　minimum maximum config_value run_value
allow updates 　　　　　0 1 0 0
clr enabled 　　　　　0 1 0 0
cross db ownership chaining 0 1 0 0
default language 　　　　　0 9999 28 28
max text repl size (B) 0 2147483647 65536 65536
nested triggers 　　　　　0 1 1 1
remote access 　　　　　0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 　　　　　0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user options 　　　　　0 32767 0 0
麻烦大家帮我看看哪里错误。。

...全文