如何让SQL 2005 CLR Trigger返回结果给Stored Procedure用? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

如何让SQL 2005 CLR Trigger返回结果给Stored Procedure用?

如何让SQL 2005 CLR Trigger返回结果给Stored Procedure用?# Database - 数据库

a*82009-03-18 07:03

1 楼

(1) Apply following CLR Trigger when delete record from Table_1
For row to be deleted
Get associated tax from external file system.
(2) Delete_Stored_Procedure　(non CLR)
DELETE from Table_1 where Year = 2008
问题是: CLR Trigger如何将Associated tax返回给Delete_Stored_Procedure?
谢谢！

j*n2009-03-18 07:03

2 楼

Is trigger necessary? you can get associated tax for records being deleted
first and then delete.

a*82009-03-18 07:03

3 楼

Assume a couple of "delete" operation may appear:
- delete from Table_1 where Year = 2008
- delete from Table_1 where Year < 2008
- delete from Table_1 where Name = 'John'
Applying an trigger on delete is a simple & direct way.

p*l2009-03-18 07:03

4 楼

Try to put into another table?

a*82009-03-18 07:03

5 楼

Yes. It should be ok to put result into another table then read it in stored
procedure. I'm wondering any other ways can help that.

j*n2009-03-18 07:03

6 楼

hehe, 2k5 new feature....
OUTPUT clause
delete from Table_1
OUTPUT DELETED.*
where Year = 2008

B*g2009-03-18 07:03

7 楼

你这个不还是要一个temp table吗？

【在 j*****n 的大作中提到】

: hehe, 2k5 new feature....
: OUTPUT clause
: delete from Table_1
: OUTPUT DELETED.*
: where Year = 2008

j*n2009-03-18 07:03

8 楼

his SP could catch this data set itself, why need temp table?

【在 B*****g 的大作中提到】

: 你这个不还是要一个temp table吗？

a*82009-03-18 07:03

9 楼

I need get "TAX" information from external file system. How can T-SQL SP get
it? I think we can re-write CLR SP, but it'll involve a lot of changes -
that's I'm trying to avoid.

B*g2009-03-18 07:03

10 楼

delete output can only get the deleted ids, then use this ids to get tax
from external source.

【在 j*****n 的大作中提到】

: his SP could catch this data set itself, why need temp table?

j*n2009-03-18 07:03

11 楼

no idea about CLR SP... can you use xp_commandshell within your T-sql sp? it
might need higher privilege though.

get

【在 a******8 的大作中提到】

: I need get "TAX" information from external file system. How can T-SQL SP get
: it? I think we can re-write CLR SP, but it'll involve a lot of changes -
: that's I'm trying to avoid.

B*g2009-03-18 07:03

12 楼

1. delete output
2. CLR user defined function (input id list）
get tax in function.
return id+tax list

it

【在 j*****n 的大作中提到】

: no idea about CLR SP... can you use xp_commandshell within your T-sql sp? it
: might need higher privilege though.
:
: get

j*n2009-03-18 07:03

13 楼

顶

【在 B*****g 的大作中提到】

: 1. delete output
: 2. CLR user defined function (input id list）
: get tax in function.
: return id+tax list
:
: it

a*82009-03-18 07:03

14 楼

THX!