how to delete the last row inserted from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to delete the last row inserted from a table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-10 : 08:17:13

nikolaos writes "hello there,

i create the following table

CREATE TABLE [dbo].[n_amount] (
[id] [varchar] (10) COLLATE Greek_CI_AS NULL ,
[description] [varchar] (20) COLLATE Greek_CI_AS NULL ,
[amount] [money] NULL
) ON [PRIMARY]
GO

i insert some values on this table.

at some point i do this query

"select sum (amount) from n_amount"

and i get this : 24

what i want to do is this: to create a trigger and every time a user inserts a row that will take the sum(amount) > 25, to delete the row and give him a message back.

to delete basically the last inserted row.

************************************************

i created the following

create TRIGGER trig_checksum

ON n_amount

FOR insert

AS

declare @sum_amount money
set @sum_amount = (select sum (amount) from n_amount)

if @sum_amount > 25
begin

print ' you can not have amount > 25 '

end

**********************************

what i want is just before i print the message to delete the row that violated the constraint as well"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-10 : 08:25:23

Use the inserted virtual table to determine which row(s) to delete.

Jay White
{0}

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-10 : 09:30:57

If you are using SQL 2000 look at using an INSTEAD OF trigger to pre-validate the data before it is inserted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources