Remove cursor from trigger - multi row updates

Remove cursor from trigger - multi row updates
I need to remove to following cursor for the delete trigger (I didn't
design it) as it is causing too much of a bottle neck on the server.
What would be the best way to impletement this, bearing in mind that it
would have to take in account multi row functionality as the system
very rarely has single row deletes. I have SQL2K Enterprise on
Win2KServer.
CREATE TRIGGER [tri_DelRecs] ON MyTable
FOR DELETE
AS
SET NOCOUNT ON
DECLARE @.MasterID int
DECLARE dc2 CURSOR FOR
SELECT [Master ID] FROM Deleted
OPEN dc2
FETCH NEXT FROM dc2 INTO @.MasterID
WHILE @.@.FETCH_STATUS = 0
BEGIN
delete from [tb1] where [master id] = @.masterid
delete from [tb2] where [master id] = @.masterid
delete from [tb3] where [master id] = @.masterid
delete from [tb4] where [master id] = @.masterid
delete from [tb5] where [master id] = @.masterid
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
delete from [tbn] where [master id] = @.masterid
FETCH NEXT FROM dc2 INTO @.Masterid
END
CLOSE dc2
DEALLOCATE dc2
Would I able to accomplish this using a series of JOINs on master id?
Thanks
qhdelete from [tb1] where [master id] in (Select [Master ID] from Deleted)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Scott" <quackhandle1975@.yahoo.co.uk> schrieb im Newsbeitrag
news:1113569793.664500.141850@.g14g2000cwa.googlegroups.com...
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> .. .. .. .. .. .. ..
> .. .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||delete [tb1]
where exists (select * from deleted as d where d.[master id] = tb1.[master
id])
...
AMB
"Scott" wrote:

> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> ... .. .. .. .. .. ..
> ... .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||Have you considered using ON DELETE CASCADE instead of a trigger?
Never use cursors in triggers, for one thing the set-based code is
usually much simpler:
CREATE TRIGGER [tri_DelRecs] ON mytable FOR DELETE
AS
DELETE FROM tb1
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb1.[master id])
DELETE FROM tb2
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb2.[master id])
... etc
David Portas
SQL Server MVP
--|||Change your DDL on the referencing tables:
CREATE TABLE subord_n
(...
master_id INTEGER NOT NULL
REFERENCES Master (master_id)
ON DELETE CASCADE,
.);
Using DRI actions also gives the optimizer information about the
relationships, so all your code gets better.|||Thanks for all your replies guys, much appreciated!
I have used been testing and I think I have what I'm after using the
following trigger, although I will try the ON DELETE CASCADE.
Cheers
qh|||Doh!
CREATE TRIGGER [tri_MyTrigger] ON [dbo].[tbl1]
FOR INSERT, UPDATE, DELETE
AS
IF @.@.ROWCOUNT = 0
BEGIN
PRINT 'There are no records to delete!'
RETURN
END
IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
INSERT INTO tbl2([ID], [Name], [Team])
SELECT [ID], [name], [Team] FROM Inserted
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
DELETE FROM tbl2
WHERE tbl2.[ID] IN (SELECT [ID] FROM Deleted)
ELSE
--sql block for update
UPDATE tbl2
SET [Team] = Inserted.[Team], [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
qh

remove cursor

hi below is my procedure..i don't want to use cursor....than what should be my query?

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

DECLARE @.Bank_Name VARCHAR(50)
DECLARE @.MICR_CMSCode varchar(50),@.MICR_SchemeGroup varchar(50)

--BANK CURSOR
DECLARE CUR_BANK CURSOR FOR
SELECT Bank_Name FROM Tbl_BankMst where Bank_isactive=1

OPEN CUR_BANK

FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
WHILE @.@.FETCH_STATUS=0
BEGIN

--print(@.Bank_Name)
--MICR CURSOR
DECLARE CUR_MICR cursor for
--Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_BankName='ICICI BANK LTD'
Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_AuthStatus =2 and MICR_Optype =0 and MICR_BankName=rtrim(ltrim(@.Bank_Name))

Open CUR_MICR

Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup
while @.@.fetch_status = 0
begin
update Tbl_CMS_UploadDetails set CMS_SchemeGroup =@.MICR_SchemeGroup Where Scheme_Code=rtrim(ltrim(@.MICR_CMSCode))

--print(@.MICR_SchemeGroup)--@.MICR_CMSCode
Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup

end
close CUR_MICR
deallocate CUR_MICR

- update compare status and maker-

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName
--select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name='ICICI BANK LTD'

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
-
FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
END

close CUR_BANK
deallocate CUR_BANK

hi try this..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||oops, i forgot to add the on clause..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||thanx let me try i will be back.sql

Remove Cursor

Hi all,
I have a table A with 2 cols one id and the other linkedids separated with
pipe "|"
id linked ids
1 4|5|6
2 9|10|11
I want the output as without using cursors or while loop..
1 4
1 5
1 6
2 9
2 10
2 11
Your thoughts on this...
Thanks in advance,
PradeepSee this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan|||And my personal favourite:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
ML|||This chy little solution can deal with up to four items ( three pipes ):
SET NOCOUNT ON
DROP TABLE #linked_ids
CREATE TABLE #linked_ids ( id INT PRIMARY KEY, linked_ids VARCHAR( 50 ) )
INSERT INTO #linked_ids
SELECT 1, '4|5|6|7' UNION
SELECT 2, '9|10|11|12' UNION
SELECT 3, '13|14|15|16|17'
SET NOCOUNT OFF
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 1 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 2 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 3 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 4)
FROM #linked_ids t
Damien
"Pradeep Kutty" wrote:

> Hi all,
> I have a table A with 2 cols one id and the other linkedids separated with
> pipe "|"
> id linked ids
> 1 4|5|6
> 2 9|10|11
> I want the output as without using cursors or while loop..
> 1 4
> 1 5
> 1 6
> 2 9
> 2 10
> 2 11
> Your thoughts on this...
> Thanks in advance,
> Pradeep
>
>|||See: http://tinyurl.com/b3ce2
Anith