October 11, 2012 at 6:37 am
Hi
i want to delete existing records from sql table, after inserting new record.
am using below code...
CREATE trigger Del_records on Active_Table
AFTER insert
as
delete from Active_Table
but it is deleting all records
plz help
October 11, 2012 at 6:43 am
Skanda (10/11/2012)
Hii want to delete existing records from sql table, after inserting new record.
am using below code...
CREATE trigger Del_records on Active_Table
AFTER insert
as
delete from Active_Table
but it is deleting all records
plz help
DELETE FROM Active_Table is an unrestricted delete - it will always delete all rows from the table, locking and blocking aside.
What are your rules for deleting rows? There must be some relationship between the rows you are inserting and those you want to delete.
Why can't you use UPDATE?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 11, 2012 at 6:45 am
hi,
as per my requeirement when ever i insert new record, existing record should be deleted.
October 11, 2012 at 6:50 am
Skanda (10/11/2012)
hi,as per my requeirement when ever i insert new record, existing record should be deleted.
What denotes "existing record"? How do you identify it?
Why don't you use UPDATE instead of inserting one row and deleting one row?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 11, 2012 at 7:32 am
As nonsensical as this requirement is, sounds like you want
CREATE trigger Del_records on Active_Table
AFTER insert
as
delete from Active_Table a WHERE a.ID NOT IN (SELECT ID FROM INSERTED)
Of course, use your column in place of "ID".
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2012 at 8:49 am
ChrisM@home (10/11/2012)
Why don't you use UPDATE instead of inserting one row and deleting one row?
Because now we get increased index fragmentation, page splits and a slower process?
_______________________________________________________________
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/
October 11, 2012 at 9:04 am
if the OP can identify how he knows the "same record" already exists, it would be something like this
CREATE trigger Del_records on Active_Table
AFTER insert
as
delete from Active_Table a
INNER JOIN INSERTED
ON a.MatchingColumn = INSERTED.MatchingColumn
AND a.ID <> INSERTED.ID
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply