help me in delete records
-
Hi all
Let i have a table named test and in that table 20 records are there
When i execute this query
delete test
those 20 records should be insert to another table before deletion
So when I got the reply that " 20 records have affected"
those 20 records must be stored in another table
Pl help me
-
Hi,
also i understand you must insert in a store Table
insert * from test into store_test
truncate table test
You must have two Tables one for deleting an one for stored
Thomas
-
no this query gives error
and i have only one table named test
the records in test table must be inserted into new table at the time of deletion
pl solve this if anyone can
-
Can you give us more details about the task at hand... seems to me like you need to use a trigger to achieve this operation.
-
-
CREATE TRIGGER tr_AuditTableName_D ON [dbo].[Test]
DELETE
AS
SET NOCOUNT ON
Insert into dbo.TableAudit (col1, col2) Select Col1, Col2 from Deleted
GO
-
CREATE TRIGGER trgInsertIntoNewTable
ON test
FOR DELETE
AS
SELECT * INTO newtable FROM deleted
GO.....?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
ok - ok remi - you'll have to give me marks for at least trying to match your speed...know it's a lost cause but still...
**ASCII stupid question, get a stupid ANSI !!!** -
CREATE TRIGGER trgInsertIntoNewTable
ON test2
FOR DELETE
AS
SELECT * INTO test3 FROM test2
GO
Sorry Sushila This trigger object is created but it gives the follwing error
Invalid object name 'test3'
So pl solve this if u can
-
No it's not a lost cause... I can be hung up somewhere else and that gives you enough time. but this one was close (assumine you meant to time insert into/select instead of select into
).Did you get my last reply to our conversation yesterday?
-
My solution is the right way to go... select into should be avoided at all costs in production.
-
CREATE TRIGGER tr_AuditTableName_D_1 ON Test2 for
DELETE
AS
SET NOCOUNT ON
Insert into test3(a, b) Select acctno, acctname from test2
GO
Sorry Remi Gregoire
ur code gives the follwing Error
"The command(s) completed successfully."
delete test2
"Invalid object name 'test3'."
So pl solve this if u can
Than'x
-
also...the from part of it should be from Deleted..
remi - i find that i'm making a lot of mistakes just so i can get there ahead of you - now that i know it's impossible i'm going to withdraw and just stick to "anything that is not sql"
ps:yes - i did get the response!
**ASCII stupid question, get a stupid ANSI !!!** -
Test3 must be created before you try to insert into it. This is a permanent audit table... Also they usually have a default field (auditDate default GetDate()).
-
smruti - are you sure you have a test3 table ?! you need to create it first!!!
FROM DELETED
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply