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

remoted linked server problem

I am running SQL 2005 trigger and update the table in remote linked server. This is working perfectly when the firewall is off. However when I turn on the firewall, it wont run any more. I need the firewall on for the security issue. How can I run this trigger with firewall on? Thanks.Do not create triggers that operate outside the scope of their own database.|||Do not create triggers that operate outside the scope of their own database.

+1

hmscott

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.Hi
"00ScarlettJohnson" wrote:
> I am running SQL 2005 trigger and update the table in remote linked server.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the security
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
>> I am running SQL 2005 trigger and update the table in remote linked
>> server.
>> This is working perfectly when the firewall is off. However when I turn
>> on
>> the firewall, it won't run any more. I need the firewall on for the
>> security
>> issue. How can I run this trigger with firewall on? Thanks.
>>
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John|||Hi
"00ScarlettJohnson" wrote:
> After I read the webpage, I am not sure if I understand correctly, I go to
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port through
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John|||Hi,
I'm just trying to update a table in a remote linked server from a trigger.
The problem is that when the trigger is fired, it gives an error: "No
transaction is active".
I'm using SQL Express 2005 for both servers. The firewall is off in both
machines, also, the MSDTS service is running.
Could you tell me if I'm missing something?
Thanks,
" 00ScarlettJohnson" <EE@.yahoo.com> escribió en el mensaje
news:uTDne%23BpHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am running SQL 2005 trigger and update the table in remote linked server.
>This is working perfectly when the firewall is off. However when I turn on
>the firewall, it won't run any more. I need the firewall on for the
>security issue. How can I run this trigger with firewall on? Thanks.
>|||Hi
Check that MSDTC is correctly configured
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql
Also make sure that the service accounts for SQL Server is a domain account
http://msdn2.microsoft.com/en-us/library/ms143504.aspx#Use_startup_accounts
If you explicitly start a DISTRIBUTED transaction before the statement that
fires the trigger do you still get the error?
John
"Anahi Ludueña" wrote:
> Hi,
> I'm just trying to update a table in a remote linked server from a trigger.
> The problem is that when the trigger is fired, it gives an error: "No
> transaction is active".
> I'm using SQL Express 2005 for both servers. The firewall is off in both
> machines, also, the MSDTS service is running.
> Could you tell me if I'm missing something?
> Thanks,
>
> " 00ScarlettJohnson" <EE@.yahoo.com> escribió en el mensaje
> news:uTDne%23BpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> >I am running SQL 2005 trigger and update the table in remote linked server.
> >This is working perfectly when the firewall is off. However when I turn on
> >the firewall, it won't run any more. I need the firewall on for the
> >security issue. How can I run this trigger with firewall on? Thanks.
> >
> >
>
>

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.Hi
"00ScarlettJohnson" wrote:

> I am running SQL 2005 trigger and update the table in remote linked server
.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the securi
ty
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
>
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John|||Hi
"00ScarlettJohnson" wrote:

> After I read the webpage, I am not sure if I understand correctly, I go t
o
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly
.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port throug
h
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John

remote linked server problem

I am running SQL 2005 trigger and update the table in remote linked server.
This is working perfectly when the firewall is off. However when I turn on
the firewall, it won't run any more. I need the firewall on for the security
issue. How can I run this trigger with firewall on? Thanks.
Hi
"00ScarlettJohnson" wrote:

> I am running SQL 2005 trigger and update the table in remote linked server.
> This is working perfectly when the firewall is off. However when I turn on
> the firewall, it won't run any more. I need the firewall on for the security
> issue. How can I run this trigger with firewall on? Thanks.
>
You will need to set up specific rules in the firewall to allow this to go
through but not anyone/anywhere else! See
http://support.microsoft.com/kb/287932
John
|||After I read the webpage, I am not sure if I understand correctly, I go to
firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
and this is refered to the "any port" from the document. And still didn't
work. Can you please help? I am really not sure if I understand correctly.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1B202C85-C436-4F6A-AA24-2F158E86E03A@.microsoft.com...
> Hi
> "00ScarlettJohnson" wrote:
> You will need to set up specific rules in the firewall to allow this to go
> through but not anyone/anywhere else! See
> http://support.microsoft.com/kb/287932
> John
|||Hi
"00ScarlettJohnson" wrote:

> After I read the webpage, I am not sure if I understand correctly, I go to
> firewall and add port 1434 TCP. And it didn't work. So I add a port 1040
> and this is refered to the "any port" from the document. And still didn't
> work. Can you please help? I am really not sure if I understand correctly.
> Thanks.
>
Port 1433 is the default port used by SQL Server and is the one registered
for use by IANA. You can configure SQL Server to use a different port through
the Server Networking Utility. If you have not done this it will be using
port 1433 (not 1434 as you specified) unless you have a named instance which
will dynamically assign a port and therefore you will need to change this in
the Server Networking Utility to use a specific port so you can set up a
firewall rule.
Clients will normally dynamically allocate a port to use to communicate with
SQL Server (although you can change this behavour using the Client Network
Utility) but the lowest port number will be 1024, therefore they may use any
port greater than or equal to 1024 to communicate with the server. Each
client can choose a different port.
If you are using the default ports you will need to configure the firewall
rules so that server can received SQL Server requests from any port (>=1024)
on the clients to port 1433 on the server, and the server can return any
communications from port 1433 to any port on the client.
Client Port -> Server Port
Dynamic >= 1024 1433
Server Port -> Client Port
1433 Dynamic Port (same port as above)
HTH
John