Experts Round Table Network

Databases => MS SQL Server => Topic started by: seandelaney on January 24, 2006, 02:58:00 AM


Title: Sql Trigger Problem not deleting last entry
Post by: seandelaney on January 24, 2006, 02:58:00 AM
SQL server 2000

This trigger basically is carried out when i delete an entry in tbltimeentry.
The trigger basicsly adds the differnet types of hours and populates an entry in tblentry with all the differnt time entry for a persons timesheet

Adding lots of entries works fine  lso when deleting entries which this trigger does works fine

BUT

When the last/only (if the is only 1 entry) entry on tbltimeentry is to be deleted the trigger seems not to fire or do the calculation

The entry is deletd from tblTimeentry but the summing calc below is not carried out

This ONLY falis when there  is ONE entry left or in time entry and has an ossociated tbtimeentry

CREATE TRIGGER Delete_BreakDown ON dbo.tblTimeEntry
FOR DELETE
AS

BEGIN

UPDATE tblEntry
SET    nTotal = x.Total_1,
             oTotal = x.Total_2,
     Total =  x.Total_1 +  x.Total_2,
     NCost = x.Cost_1,
     OCost = x.Cost_2,
     TotalCost = x.cost_1 + x.cost_2,
     TotalSales = x.Sales
FROM   tblEntry  a
JOIN   (
     SELECT  b.EntryID,
          sum( case c.RateID when 1 then b.Total else 0 end ) as Total_1 ,
          sum( case c.RateID when 2 then b.Total else 0 end ) as Total_2,
          sum( case c.RateID when 1 then b.TotalCost else 0 end ) as Cost_1,
          sum( case c.RateID when 2 then b.TotalCost else 0 end ) as Cost_2,
          sum(b.Totalsales)as Sales
     FROM   deleted    i
     JOIN   tblTimeEntry   b ON b.EntryID = i.EntryID
     JOIN   tblCost  c ON c.CostID = b.CostRateID
     GROUP BY b.EntryID      
     ) as X on x.EntryID = a.EntryID


Thanks you for any help

Title: Sql Trigger Problem not deleting last entry
Post by: Anonymous on January 24, 2006, 03:25:26 AM
One of the way I test triggers is to use the MS Query Analyser.

Try altering the trigger to NOT do the update, just the select.

You will need to STOP using the main app as you will probably get all sorts of errors.

Also, is it necessary to link the tblTimeEntry in?

Try this as the UPDATE statement.

Code:
CREATE TRIGGER
Delete_BreakDown
ON
dbo.tblTimeEntry
FOR
DELETE
AS

UPDATE
tblEntry
SET
nTotal = x.Total_1,
oTotal = x.Total_2,
Total = x.Total_1 + x.Total_2,
NCost = x.Cost_1,
OCost = x.Cost_2,
TotalCost = x.cost_1 + x.cost_2,
TotalSales = x.Sales
FROM
tblEntry INNER JOIN
(
SELECT
deleted.EntryID,
SUM( CASE tblCost.RateID WHEN 1 THEN deleted.Total ELSE 0 END ) AS Total_1 ,
SUM( CASE tblCost.RateID WHEN 2 THEN deleted.Total ELSE 0 END ) AS Total_2,
SUM( CASE tblCost.RateID WHEN 1 THEN deleted.TotalCost ELSE 0 END ) AS Cost_1,
SUM( CASE tblCost.RateID WHEN 2 THEN deleted.TotalCost ELSE 0 END ) AS Cost_2,
SUM( tblTimeEntry.Totalsales ) AS Sales
FROM
deleted INNER JOIN tblCost ON tblCost.CostID = deleted.CostRateID
GROUP BY
deleted.EntryID
) AS x ON x.EntryID = tblEntry.EntryID

Title: Sql Trigger Problem not deleting last entry
Post by: Anonymous on January 24, 2006, 03:32:08 AM
Quote from: "Microsoft's Books Online"
AFTER

Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.

AFTER is the default, if FOR is the only keyword specified.

AFTER triggers cannot be defined on views.


If I read this correctly, the trigger is performed AFTER the data has been deleted.

This is why you are losing 1 record. I suspect that ALL the records you are updating MAY be wrong.

More so because you are linking in the tblTimeEntry which is at least 1 row short!

The query I gave is NOT linking in tblTimeEntry, but instead is using the deleted table only as this table WILL contain the deleted rows from tblTimeEntry.

I hope this helps and apologies for stepping on the resident Mentor here.

Title: Sql Trigger Problem not deleting last entry
Post by: seandelaney on January 24, 2006, 04:11:52 AM
Thank you for you help i have tried you trigger but still have the same problem.

In your second post is the trigger ment to be performed on a delete trigger or  not.

Title: Sql Trigger Problem not deleting last entry
Post by: Anonymous on January 24, 2006, 04:26:23 AM
Quote from: "seandelaney"
In your second post is the trigger ment to be performed on a delete trigger or  not.


Yes.

Have you tried deleting a row using the Query Analyser?

This will show you any errors that the trigger is encountering.

Something you may like to try is to remove the UPDATE part of the trigger and just use the SELECT part to see what is being returned.

Code:
CREATE TRIGGER
   Delete_BreakDown
ON
   dbo.tblTimeEntry
FOR
   DELETE
AS


SELECT
*
FROM
deleted


SELECT
 deleted.EntryID,
 SUM( CASE tblCost.RateID WHEN 1 THEN deleted.Total ELSE 0 END ) AS Total_1 ,
 SUM( CASE tblCost.RateID WHEN 2 THEN deleted.Total ELSE 0 END ) AS Total_2,
 SUM( CASE tblCost.RateID WHEN 1 THEN deleted.TotalCost ELSE 0 END ) AS Cost_1,
 SUM( CASE tblCost.RateID WHEN 2 THEN deleted.TotalCost ELSE 0 END ) AS Cost_2,
 SUM( tblTimeEntry.Totalsales ) AS Sales
FROM
 deleted INNER JOIN tblCost ON tblCost.CostID = deleted.CostRateID
GROUP BY
 deleted.EntryID


When a tblTimeEntry is deleted, you will get 2 result sets. One is the actual data from the deletion, the other is the data used to update the tblEntry table, but without actually updating the table.

The purpose of this is to make sure that what is being deleted is being used and not something else.

You will need to set this trigger as above and then use an SQL statement in QA to delete a row from tblTimeEntry.

BEWARE: You REALLY must be working on a demo database for this sort of testing as I REALLY don't want to be the one to tell you that you've just wiped all the data!!!!

Title: Sql Trigger Problem not deleting last entry
Post by: seandelaney on January 24, 2006, 05:44:38 AM
I used the SQL query analyser
here is the result set

way i read that is the update is done first then the delete

Title: Sql Trigger Problem not deleting last entry
Post by: seandelaney on January 24, 2006, 05:45:27 AM
SET STATISTICS PROFILE ON    
CREATE TRIGGER DeleteMisc_BreakDown ON dbo.tblMiscEntry   FOR DELETE  AS    
UPDATE tblEntry    SET    OverheadNTotal = x.Total_1,               OverheadOTotal = x.Total_2,               OverheadTotal =  x.Total_1 +  x.Total_2,               OverNcost = x.Cost_1,              OverOcost =  x.Cost_2,            
delete  from dbo.tblMiscEntry where MiscEntryID = 166  
SET STATISTICS PROFILE OFF

Title: Sql Trigger Problem not deleting last entry
Post by: Anonymous on January 24, 2006, 06:17:11 AM
Quote from: "seandelaney"
I used the SQL query analyser
here is the result set

way i read that is the update is done first then the delete


Quote
Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully.


Your code is the trigger.

The SQL containing the DELETE statement is the triggering SQL.

The DELETE takes place FIRST!!!!! That is why there is a DELETED and an INSERTED result set to work with.


Here is some proof.

This SQL will create a DB, a table, add a trigger.

Code:
/****** Object:  Database Dummy    Script Date: 24/01/2006 13:07:38 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Dummy')
DROP DATABASE [Dummy]
GO

CREATE DATABASE [Dummy]  ON (NAME = N'Dummy_Data', FILENAME = N'D:\Data\SQL 2000\MSSQL\Data\Dummy_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'Dummy_Log', FILENAME = N'D:\Data\SQL 2000\MSSQL\Data\Dummy_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Latin1_General_CI_AS
GO

exec sp_dboption N'Dummy', N'autoclose', N'false'
GO

exec sp_dboption N'Dummy', N'bulkcopy', N'false'
GO

exec sp_dboption N'Dummy', N'trunc. log', N'false'
GO

exec sp_dboption N'Dummy', N'torn page detection', N'true'
GO

exec sp_dboption N'Dummy', N'read only', N'false'
GO

exec sp_dboption N'Dummy', N'dbo use', N'false'
GO

exec sp_dboption N'Dummy', N'single', N'false'
GO

exec sp_dboption N'Dummy', N'autoshrink', N'false'
GO

exec sp_dboption N'Dummy', N'ANSI null default', N'false'
GO

exec sp_dboption N'Dummy', N'recursive triggers', N'false'
GO

exec sp_dboption N'Dummy', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Dummy', N'concat null yields null', N'false'
GO

exec sp_dboption N'Dummy', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Dummy', N'default to local cursor', N'false'
GO

exec sp_dboption N'Dummy', N'quoted identifier', N'false'
GO

exec sp_dboption N'Dummy', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Dummy', N'auto create statistics', N'true'
GO

exec sp_dboption N'Dummy', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'Dummy', N'db chaining', N'false'
GO

use [Dummy]
GO

/****** Object:  Trigger dbo.DeleteRowCount    Script Date: 24/01/2006 13:07:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteRowCount]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[DeleteRowCount]
GO

/****** Object:  Table [dbo].[DummyTable]    Script Date: 24/01/2006 13:07:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DummyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DummyTable]
GO

/****** Object:  Table [dbo].[DummyTable]    Script Date: 24/01/2006 13:07:39 ******/
CREATE TABLE [dbo].[DummyTable] (
[dummyID] [int] IDENTITY (1, 1) NOT NULL ,
[A_Timestamp] [timestamp] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DummyTable] WITH NOCHECK ADD
CONSTRAINT [PK_DummyTable] PRIMARY KEY  CLUSTERED
(
[dummyID]
)  ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object:  Trigger dbo.DeleteRowCount    Script Date: 24/01/2006 13:07:39 ******/
CREATE TRIGGER DeleteRowCount ON [dbo].[DummyTable]
FOR DELETE
AS

/* First return the number of rows in the DELETED set */
SELECT
'Deleted Rows Count',
COUNT(*) AS [Count]
FROM
deleted
UNION
/* Then return the number of rows in the DummyTable */
SELECT
'DummyTable Rows Count',
COUNT(*) AS [Count]
FROM
DummyTable

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Next, I insert 40 rows and then run a 3 specific queries.

[list=1]
  • Show the number of rows in the table.
  • Delete 1 row
  • Show the number of rows in the table.
    • [/list:o]

Code:
/* Insert 40 records */

INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)
INSERT DummyTable(A_Timestamp) VALUES(DEFAULT)


/* How many rows do we have ? */
SELECT COUNT(*) AS [Before Delete] FROM DummyTable

/* Delete one of them */
DELETE FROM DummyTable WHERE DummyID=1

/* How many rows do we have now ? */
SELECT COUNT(*) AS [After Delete] FROM DummyTable


You will see that whilst executing the trigger, the row has already been deleted and therefore cannot be accessed.

My result sets for the 3 specific queries are :

Set 1:
Returns 1 row with 1 column.
Column 1, Row is "40.00"

Set 2:
Returns 2 rows with 2 columns.
Column 1, Row 1 is "Deleted Rows Count"
Column 2, Row 1 is "1.00"
Column 1, Row 2 is "DummyTable Rows Count"
Column 2, Row 2 is "39.00"

Set 3:
Returns 1 row with 1 column.
Column 1, Row is "39.00"


If I alter the delete query to now say
Code:
/* How many rows do we have ? */
SELECT COUNT(*) AS [Before Delete] FROM DummyTable

/* Delete one of them */
DELETE FROM DummyTable WHERE DummyID<10

/* How many rows do we have now ? */
SELECT COUNT(*) AS [After Delete] FROM DummyTable


The results are:

Set 1:
Returns 1 row with 1 column.
Column 1, Row is "39.00"

Set 2:
Returns 2 rows with 2 columns.
Column 1, Row 1 is "Deleted Rows Count"
Column 2, Row 1 is "8.00"
Column 1, Row 2 is "DummyTable Rows Count"
Column 2, Row 2 is "31.00"

Set 3:
Returns 1 row with 1 column.
Column 1, Row is "31.00"

This shows that you cannot look at the source table when you are deleting data. You can only look at the DELETED table as this contains the deleted rows.

Title: Sql Trigger Problem not deleting last entry
Post by: seandelaney on January 24, 2006, 07:58:47 AM
I totally understand

but

This driving me mad

i show using table whats hapning

TBlTime entry     RateID 1 = nTotal  

ID   EntryID  Total  Rateid
1        1          5        1
2        1          3        1

So the entry on TBlTime

(Looks like this)
EntryID  nTotal  
1               8

I delete the second row in tbl time entry ID 2
So the entry on TBlTime

Looks like this
EntryID  nTotal  
1               5

(This works and if i add more entries and take them away is also work fine.)

If i delete  
TBlTime entry     ID 1

Looks STILL  like this not taking the 5 hrs off it only when there is one entry
EntryID  nTotal  
1               5


SO when it does its sum it will sum null or zero i think this may be my problem i dont know?

Title: Sql Trigger Problem not deleting last entry
Post by: Anonymous on January 24, 2006, 08:03:47 AM
Daft question.

Are you basically archiving the totals?

By this I mean as you delete a row you are adding the data to another table?

If so, the UPDATE statement is simply overwriting the values!!!

You need to use

SET column = colum + value from result set.


Powered by SMF 1.1 RC2 | SMF © 2001-2005, Lewis Media
Joomla Bridge by JoomlaHacks.com