Passing cursors from trigger to sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing cursors from trigger to sp

Author

Topic

tswern
Starting Member

10 Posts

Posted - 2003-05-26 : 23:40:15

Hi all,

Is it possible to pass cursors from a trigger to a stored proc? Would like to do that because I am thinking of writing a validation program stored procedure which can be called by the trigger, however, there are quite a number of arguments to pass in.

Thanks

Save the dugongs!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-27 : 02:52:08

Hi:

Well, yes, it is possible to pass a cursor to a stored procedure (the source doesnt really matter), but the cursor must be declared as a VARYING OUTPUT parameter in the sproc:

CREATE PROCEDURE proc_foo @myCursor CURSOR VARYING OUTPUT, @Param1 INT...etc

But this approach is certainly not recommended. Cursors are slow and place a large overhead on server resources. If there is a set-based way of doing what you want to do, you are a lot better off using that method...cursors should be your last choice. The problem is aggrevated by the fact that you are planning to call the proc from a trigger - creating cursors is definitely not recommended inside a trigger.

Post your DDL and required validations, and we might just be able to save the dugongs! (real cute creatures, aint they?!)

OS

tswern
Starting Member

10 Posts

Posted - 2003-05-27 : 05:00:24

Hi,

Here's the trigger. Comments welcomed :).

Thanks for your time!

CREATE TRIGGER EALALLITPELSTMPNRCPPF_TRIGGER ON dbo.EALALLITPELSTMPNRCPPF
INSTEAD OF INSERT
AS
Declare @micr as varchar(20),
@ref as varchar(20),
@sys_cde as char(3),
@mod_cde as char(2),
@ope_cde as char(1),
@count_cde as varchar(50),
@nm as varchar(100),
@town_city as varchar(50),
@ctr_cde as char(2),
@nr_sec_cde as char(2),
@rel_ship_cde as char(2),
@fl_nm as varchar(100),
@errMsg as varchar(500),
@total_errors tinyint,
@col as varchar(500),
@crt_dt as datetime,
@err_cde as int,
@src_tbl_nm as varchar (30),
@batch_id as varchar (50)

DECLARE readCursor CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC
for SELECT
ref,
micr,
sys_cde,
mod_cde,
ope_cde,
count_cde,
nm,
town_city,
ctr_cde,
nr_sec_cde,
rel_ship_cde,
fl_nm,
crt_dt,
src_tbl_nm,batch_id
from inserted

Open readCursor

Fetch NEXT FROM readCursor into
@ref, @micr,@sys_cde,
@mod_cde,@ope_cde,
@count_cde,@nm,
@town_city,@ctr_cde,
@nr_sec_cde,@rel_ship_cde,@fl_nm,
@crt_dt,@src_tbl_nm,
@batch_id

While(@@FETCH_STATUS=0)
BEGIN
SET @ope_cde =RTRIM(ISNULL(@ope_cde,''))
SET @count_cde = RTRIM(ISNULL(@count_cde,''))
SET @nm = RTRIM(ISNULL(@nm,''))
SET @town_city=RTRIM(ISNUll(@town_city,''))
SET @nr_sec_cde = RTRIM(ISNULL(@nr_sec_cde,''))
SET @rel_ship_cde = RTRIM(ISNULL(@rel_ship_cde,''))
SET @fl_nm = RTRIM(ISNULL(@fl_nm,''))
SET @errMsg =''
SET @col =''
SET @total_errors =0

--CHECK OPERATION CODE

IF len(@ope_cde)<>1 or(@ope_cde <>'A' and @ope_cde <>'D' and @ope_cde <>'U')
BEGIN
SET @col = @col+'ope_cde|'
SET @errMsg = @errMsg + 'INVALID OPERATION CODE | '
SET @total_errors = @total_errors +1
END
-- END CHECK OPERATION CODE
-- CHECK COUNTRY CODE

IF DATALENGTH(@ctr_cde)<> 2 OR @ctr_cde='MY' OR NOT EXISTS( SELECT CTR_CDE FROM EDISALLCNTRYCDE WHERE CTR_CDE =@ctr_cde)
BEGIN
SET @col = @col +'ctr_cde|'
SET @errMsg = @errMsg + 'INVALID COUNTRY CODE | '
SET @total_errors = @total_errors +1
END

--END CHECK COUNTRY CODE
--CHECK CUSTOMER NAME
IF DATALENGTH(@nm) = 0 OR DATALENGTH(@nm) > 100
BEGIN
SET @col = @col +'nm|'
SET @errMsg = @errMsg + 'INVALID CUST NAME | '
SET @total_errors = @total_errors +1
END
--END CUSTOMER NAME
IF NOT EXISTS( SELECT REL_CDE FROM EDISALLRELTBL WHERE REL_CDE =@rel_ship_cde)
BEGIN
SET @col = @col +'rel_ship_cde|'
SET @errMsg = @errMsg + 'INVALID RELATIONSHIP CODE | '
SET @total_errors = @total_errors +1
END

IF DATALENGTH(@fl_nm)=0 OR DATALENGTH(@fl_nm)>100
BEGIN
SET @col =@col + 'fl_nm|'
SET @errMsg = @errMsg + 'INVALID FILE NAME | '
SET @total_errors = @total_errors +1
END

IF DATALENGTH(@nr_sec_cde) = 0 or NOT EXISTS (select sect_cde from EDISALLECONSECTNR where sect_cde =@nr_sec_cde)
BEGIN
SET @col = @col +'nr_sec_cde|'
SET @errMsg = @errMsg + 'INVALID NR SECTOR CODE | '
SET @total_errors = @total_errors +1

END

IF @total_errors > 0
BEGIN
INSERT INTO EDISALLERRDETAIL
(batch_id,ref,src_tbl_nm, cols, err_msg, rec_dt, err_flg_nm)
values
(@batch_id,@ref,@src_tbl_nm,@col,@errMsg,@crt_dt,'NRCTRPP_FLG')

UPDATE EALNRCTRPP SET NRCTRPP_FLG = 1 WHERE REF = @ref
END
ELSE
BEGIN
INSERT INTO EALALLITPELSTMPNRCPPF
(ref,micr, sys_cde,mod_cde,ope_cde,
count_cde,nm,town_city,ctr_cde,
nr_sec_cde,rel_ship_cde,fl_nm,crt_dt,batch_id,src_tbl_nm)
VALUES (@ref,@micr,@sys_cde,@mod_cde,@ope_cde,
@count_cde,@nm,@town_city,@ctr_cde,
@nr_sec_cde,@rel_ship_cde,@fl_nm,@crt_dt,@batch_id,@src_tbl_nm
)

UPDATE EALNRCTRPP SET NRCTRPP_FLG = 3 WHERE REF=@ref

If @@ERROR <>0
Begin
Print @@Error
End

END
Fetch NEXT FROM readCursor into
@ref, @micr,@sys_cde,
@mod_cde,@ope_cde,
@count_cde,@nm,
@town_city,@ctr_cde,
@nr_sec_cde,@rel_ship_cde,@fl_nm,
@crt_dt,@src_tbl_nm,
@batch_id
END --Cursor loop
Close readCursor
Deallocate readCursor

Save the dugongs!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-28 : 10:57:24

Hi:

After having gone through the code of the trigger, I think there is very little that cannot be done using conventional CHECK CONSTRAINTS and FORIEGN KEY validations. Look at this example:


create table #foo
(
cola varchar(50),
colb varchar(50)
)

--Only allow A, D or U
alter table #foo add constraint abc check (cola IN('A','D','U'))

--works
insert into #foo values('A','AA')

--fails
insert into #foo values('ABD','AA')

That takes care of code like this "IF len(@ope_cde)<>1 or(@ope_cde <>'A' and @ope_cde <>'D' and @ope_cde <>'U') "

And in the places where you have this type of code "EXISTS( SELECT CTR_CDE FROM EDISALLCNTRYCDE WHERE CTR_CDE =@ctr_cde)", you should let SQL Server handle the checks for you. It looks like you dont have Foreign Key relations between the tables. If you have a relation between say, CountryID in Customers Table and CountryID in the Country Table, SQL Server will not let you insert a customer with a CountryID that does not exist in the Country Table. Read about it in the Books on line

HTH

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources