求数据从一个表导人另一个表时的触发器(100分)

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-14

• #1

表 MYTABLE 建有触发器（KEYID 输入0时自动增加） 用
INSERT MYTABLE (KEYID,DEPTNAME) VALUES (0,'ABC')
增加数据触发器正常、而用下面语句从一个表导人另一个表时
INSERT MYTABLE (KEYID,DEPTNAME) SELECT 0,DEPTNAME FROM OTTABLE
触发器不正常
INSERT ....... SELECT 语句的触发器什么条件下才会出发？

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-14

• #2

贴出来的你的触发器看看.

 

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-14

• #3

CREATE TRIGGER TRI_MYTABLE_INS ON MYTABLE FOR INSERT AS
BEGIN
DECLARE @VID NUMERIC(5)
SELECT @VID = ISNULL(MAX(KEYID ),0)+1 FROM MYTABLE
UPDATE MYTABLE SET KEYID= @VID WHERE KEYID = 0
END
----------------------------
用 INSERT MYTABLE (KEYID,DEPTNAME) VALUES (0,'ABC') 上面正常
KEYID 会自动增加为1、2、3、4、5……
用 INSERT MYTABLE (KEYID,DEPTNAME) SELECT 0,DEPTNAME FROM OTTABLE
就不正常了 KEYID 都是 0 没有触发 OTTABL 和 MYTABL 是结构一样的表

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-14

• #4

没发现问题,呵呵.

你要实现的这个功能可以用触发器吧.
在create table的时候可以绑定函数,一样能达到这个功能.

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-14

• #5

奇怪了我试验可以啊,呵呵
declare @t table (id int,name varchar(100))
insert into @t select 0,'aa'
union all select 0,'aa'
--select * from @t
insert into mytable (keyid,name) select 0,name from @t

select * from mytable
delete from mytable

 

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-14

• #6

to hityou
一行没有问题啊。OTTABLE有很多数据呀

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #7

你的KEYID如果是自增的可以不用插入值
insert into mytable (name) select name from @t

 

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #8

To 李翔鹏
KEYID是要下面触发器自动加的。每次只需要递0
CREATE TRIGGER TRI_MYTABLE_INS ON MYTABLE FOR INSERT AS
BEGIN
DECLARE @VID NUMERIC(5)
SELECT @VID = ISNULL(MAX(KEYID ),0)+1 FROM MYTABLE
UPDATE MYTABLE SET KEYID= @VID WHERE KEYID = 0
END
因为表是原来的。结构不能改了。只是想优化代码。

 

Z

zdwjetlee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #9

触发器里面加游标啊

 

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #10

to zdwjetlee
如何加游标？

 

X

xie0930

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #11

我都存过19M
if OpenDialog1.Execute then
begin
if OpenDialog1.FileName = '' then Exit;
FileName := OpenDialog1.FileName;
Mem := TMemoryStream.Create;
Mem.LoadFromFile(FileName);
Mem.Seek(0,soFromBeginning);
Query1.Close;
Query1.DatabaseName := 'D:/BLOB';
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('INSERT INTO BLOBNAME(NAME,FILENAMES) VALUESNAME,:FILENAMES)');
Query1.ParamByName('Name').AsString := FileName;
Query1.ParamByName('FileNAMES').LoadFromStream(Mem,ftBlob);
Query1.ExecSQL;
Mem.Free;
end;
如何取出blob字段內容并保存一個新的文件???

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #12

INSERT MYTABLE (KEYID,DEPTNAME) SELECT 0,DEPTNAME FROM OTTABLE 这句话改为
INSERT MYTABLE (KEYID,DEPTNAME) SELECT identity(int,1,1) as keyid DEPTNAME FROM OTTABLE

 

Z

zdwjetlee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-15

• #13

declare @new_billid t_keyid, @new_billkind t_integer, @new_billstate t_integer…… //Variable definitions
declare new_cursor cursor local forward_only static optimistic for
select billid,billkind,billstate…… from inserted //cursor definitions
open new_cursor //open cursor
fetch next from new_cursor into @new_billid , @new_billkind , @new_billstate //get first record
while (@@fetch_status = 0) //while not end
begin
if (……)
……
//what you want

fetch next from new_cursor into @new_billid , @new_billkind , @new_billstate //get next record
end
close new_cursor //close the cursor
deallocate new_cursor //free the cursor

对数据集合作出处理

 

L

loujing

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-16

• #14

加游标

 

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-16

• #15

不优化代码了[]