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

Remote SQL Trace causing network slowness

Hello!
I was running SQL Trace from my computer (remotely) and noticed that all
activities against this server are slowing down. Queries that normally takes
a sec to execute are taking much longer. Once I stop trace, everything is
back to normal. Local Area Connection graph show network activity under
0.5%.
I have no problems when I start trace locally on the second node of the
cluster (server in question is SQL Server 2005 64-bit /Windows 2003 SP1
Active/Passive cluster). Did someone experience similar problem? What is the
best practices in regards to executing SQL Trace to minimize impact on SQL
server?
Thanks,
IgorNever run profiler on a busy system if you are concerned with performance,
especially remotely. Instead use Trace and send the results to a local
disk. Copy those files to your local server and load them into profiler if
you want to look at them.
http://support.microsoft.com/?id=283790
--
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:uq%23PELbkGHA.4284@.TK2MSFTNGP05.phx.gbl...
> Hello!
> I was running SQL Trace from my computer (remotely) and noticed that
> all activities against this server are slowing down. Queries that normally
> takes a sec to execute are taking much longer. Once I stop trace,
> everything is back to normal. Local Area Connection graph show network
> activity under 0.5%.
> I have no problems when I start trace locally on the second node of the
> cluster (server in question is SQL Server 2005 64-bit /Windows 2003 SP1
> Active/Passive cluster). Did someone experience similar problem? What is
> the best practices in regards to executing SQL Trace to minimize impact on
> SQL server?
> Thanks,
> Igor
>|||Thank you, Andrew!
Igor
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234eaCcbkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> Never run profiler on a busy system if you are concerned with performance,
> especially remotely. Instead use Trace and send the results to a local
> disk. Copy those files to your local server and load them into profiler if
> you want to look at them.
> http://support.microsoft.com/?id=283790
> --
> Andrew J. Kelly SQL MVP
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:uq%23PELbkGHA.4284@.TK2MSFTNGP05.phx.gbl...
>> Hello!
>> I was running SQL Trace from my computer (remotely) and noticed that
>> all activities against this server are slowing down. Queries that
>> normally takes a sec to execute are taking much longer. Once I stop
>> trace, everything is back to normal. Local Area Connection graph show
>> network activity under 0.5%.
>> I have no problems when I start trace locally on the second node of the
>> cluster (server in question is SQL Server 2005 64-bit /Windows 2003 SP1
>> Active/Passive cluster). Did someone experience similar problem? What is
>> the best practices in regards to executing SQL Trace to minimize impact
>> on SQL server?
>> Thanks,
>> Igor
>