June 6, 2014 at 1:26 pm
Hello everyone. I have a problem and hopefully help me.
I created a delete trigger after the time of the delete of a table in this trigger and I make a cursor in which I get from the table and deleted the ID command to a procedure that changes my field in another table.
Within the cursor as you mentioned I get the ID of the deleted table and get this ID by other data (@ TotalEnvio ) from another table and that data as parameters to the command procedure.
trigger within the code is as follows:
alter trigger triggerDeleted ON TABLE
alter delete Not For Replication
as
begin
declare @ ID BIGINT, @TotalEnvio decimal (18,2)
declare Browse cursor for
select ID from deleted
Open Browse
Fetch Next From Browse into @ID
While @ @ fetch_status = 0
Begin
select @ TotalEnvio = TotalEnvio from Table where ID = @ ID
IF ISNULL (@ TotalEnvio, 0) = 0
SET @ TotalEnvio= 0
exec procedure @ ID, @ TotalEnvio
Fetch Next From Browse into @ID
end
Close Browse
Deallocate Browse
end
----------------------------------------------------------------------------------------------------------------------
in this proceeding,
alter procedure procedure
@ ID bigint,
@ TotalRecibo decimal (18,2)
begin
declare @TotalBusco decimal (18,2)
select @ TotalBusco = SUM (Total) from table ('of which I am removing') where id = @ ID (I get)
IF ISNULL (@ TotalBusco, 0) = 0
SET @ TotalBusco= 0
IF (@TotalBusco = @ TotalRecibo )
begin
UPDATE TABLE SET FIELD = 1 WHERE ID = @ ID
end
ELSE
BEGIN
UPDATE TABLE SET FIELD = 2 WHERE ID = @ ID
END
end
---------------------------------------------------------------------------------------------------------------------
This is the behavior or functionality of my procedure and my trigger.
But the problem is that when I manually delete me has to affect the table at which you do the update. and must enter the ELSE on the grounds that one of those records which I do summation is that I'm removing and block IF must not be equal.
Now check for NULL when I leave come variables to 0 just in case but still.
And the biggest problem is that me and serves me when I send him there if production fails.
I really hope and help me. Thanks
June 6, 2014 at 2:00 pm
Hi and welcome to the forums. What you have here is a performance timebomb. Cursors are notoriously bad for performance and inside a trigger it is a recipe for disaster. We can help you turn this RBAR (row by agonizing row) process into an efficient set based solution.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2014 at 4:05 pm
Hello.
I have removed the cursor and declare a temporary table where we perform data insert the deleted table and I make a while where I walk every record in the temporary table and in the cycle knob to call the stored procedure.
ALTER TRIGGER triggerDeleted
ON Table1
AFTER DELETE Not For Replication
AS
BEGIN
DECLARE @V_IDBIGINT,
@V_TotalAbonoDECIMAL(18,2)
CREATE TABLE #Deleter(
ID BIGINT,
rowBIGINT IDENTITY (1,1) NOT NULL
)
DECLARE @Count BIGINT=1;
INSERT INTO #Deleter
SELECT ID FROM deleted
WHILE @Count<= (SELECT COUNT(*) FROM #Deleter)
BEGIN
SELECT @V_ID=ID FROM #Deleter WHERE row = @Count
SELECT @V_TotalAbono = Ttotal
FROM Table2
WHERE ID = @V_ID
IF ISNULL(@V_TotalAbono,0)=0
SET @V_TotalAbono=0
EXEC procedure @V_ID,@V_TotalAbono
SET @Count=@Count+1
END
END
In this way the better the performance of my process?
June 7, 2014 at 7:06 pm
Can you explain what the trigger was supposed to accomplish? I didn't write your code, so I'm not sure what it's intended to do. If we had that, someone here could come up with a better way of doing what you are attempting.
June 9, 2014 at 7:43 am
boks_18 (6/6/2014)
Hello.I have removed the cursor and declare a temporary table where we perform data insert the deleted table and I make a while where I walk every record in the temporary table and in the cycle knob to call the stored procedure.
ALTER TRIGGER triggerDeleted
ON Table1
AFTER DELETE Not For Replication
AS
BEGIN
DECLARE @V_IDBIGINT,
@V_TotalAbonoDECIMAL(18,2)
CREATE TABLE #Deleter(
ID BIGINT,
rowBIGINT IDENTITY (1,1) NOT NULL
)
DECLARE @Count BIGINT=1;
INSERT INTO #Deleter
SELECT ID FROM deleted
WHILE @Count<= (SELECT COUNT(*) FROM #Deleter)
BEGIN
SELECT @V_ID=ID FROM #Deleter WHERE row = @Count
SELECT @V_TotalAbono = Ttotal
FROM Table2
WHERE ID = @V_ID
IF ISNULL(@V_TotalAbono,0)=0
SET @V_TotalAbono=0
EXEC procedure @V_ID,@V_TotalAbono
SET @Count=@Count+1
END
END
In this way the better the performance of my process?
This may be even worse from a performance perspective. You replace one looping mechanism with another. Unless you post some details for us to work with you aren't likely to get any help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2014 at 5:00 pm
ALTER TRIGGER triggerDeleted
ON [table_name]
AFTER DELETE NOT FOR REPLICATION
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max);
SET @sql = (SELECT
'EXEC [procedure_name] ' + CAST(ID AS varchar(10)) + ', ' + CAST(TotalEnvio AS varchar(30)) + '; '
FROM deleted
FOR XML PATH('')
);
EXEC(@sql);
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply