菜鸟，求一个触发器写法

[batman1983]

有一个张表A（字段typeCd, value）,另一张表B（typeCd, value1, value2）
现在想在表A上加一个触发器，表A每增加一条数据，用表A的typeCd和value，去表B查询一下。
如果表A的typeCd等于表B的typeCd,并且表A的value范围不在表B的value1到value2之间的话。
就往表C中插入一条数据。
求教了

[唐诗三百首]

就往表C中插入一条数据。 --> 请问表C什么结构?

[batman1983]

引用 1 楼 ap0405140 的回复:

就往表C中插入一条数据。 --> 请问表C什么结构?

表C结构和表A不一样，但是也有typeCd, value

[唐诗三百首]

try this,

create trigger tr_taba on 表A
after insert
as
begin
 insert into 表C(typeCd,value)
  select typeCd,value 
  from inserted a
  where not exists
  (select 1 from 表B b 
   where b.typeCd=a.typeCd 
   and (b.value1=a.value or b.value2=a.value))
end

[jack15850798154]

CREATE TABLE A
(
   TYPECD VARCHAR(20),
   [VALUE] INT	
)

INSERT INTO A
SELECT '1','20'

CREATE TABLE B
(
   TYPECD VARCHAR(20),
   VALUE1 INT,
   VALUE2 INT	
)

INSERT INTO B
SELECT '1','10','30'

CREATE TABLE C
(
   --字段未知
   COL VARCHAR(20)	
)



CREATE TRIGGER TRIGGER_A_INSERT
ON A
FOR INSERT
AS
BEGIN
	INSERT INTO C
	SELECT A.TYPECD FROM B 
	INNER JOIN A ON A.TYPECD=B.TYPECD
	WHERE A.[VALUE] BETWEEN B.VALUE1 AND B.VALUE2
END


INSERT INTO A
SELECT '1','12'

[jack15850798154]

ALTER TRIGGER TRIGGER_A_INSERT
ON A
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO C
	SELECT A.TYPECD FROM B 
	INNER JOIN INSERTED A ON A.TYPECD=B.TYPECD
	WHERE A.[VALUE] BETWEEN B.VALUE1 AND B.VALUE2
END

[jack15850798154]

不好意思题目好像看错了

CREATE TRIGGER TRIGGER_A_INSERT ON A 
FOR INSERT
AS
BEGIN    
INSERT INTO C     
SELECT A.TYPECD FROM B      
INNER JOIN A ON A.TYPECD=B.TYPECD     
WHERE A.[VALUE] NOT BETWEEN B.VALUE1 AND B.VALUE2 
END

[524929657雯]

这样试试

[习惯性蹭分]

当你的C表跟A表结构一样的吧

create trigger A_tri on A
 after insert
as begin
if exists(select 1 from B join inserted i 
on b.typecd=i.typecd and(i.value<b.value or i.val>b.value))
insert into c
select typecd,value from insered
end

[jack15850798154]

CREATE TRIGGER TRIGGER_A_INSERT ON A  
FOR INSERT AS
BEGIN    
	INSERT INTO C      
	SELECT A.TYPECD FROM B       
	INNER JOIN Inserted A ON A.TYPECD=B.TYPECD      
	WHERE A.[VALUE] NOT BETWEEN B.VALUE1 AND B.VALUE2  
END