SQL Server - update/insert

Asked By asif hameed
25-Apr-10 02:26 PM
Hi all,

I have a strored procedure which has an update statement to update all student records. But before or after updating all the records in that table, I want to stored changed/modified rows in another table. How can I do these both things ?

Regards,
Asif Hameed
  Peter Bromberg replied to asif hameed
25-Apr-10 02:33 PM
YOU can put the code to insert the target row in the other table before you have the code which does the actual INSERT or UPDATE.

Or you can write a trigger. You can look up how to use triggers in SQL Server Books Online.
  Mohan Raj Aryal replied to asif hameed
25-Apr-10 04:00 PM
I would agree on Pete's suggestion to write trigger for your requirement. Trigger is a kind of stored procedure that executes automatically when a user attempts to modify the data using some DML(INSERT, UPDATE or DELETE) statements on a table.

There are 3 types of triggers which gets activated: BEFORE, AFTER, and INSTED OF. You can use BEFORE trigger and AFTER trigger in your scenario to keep the old records. 

http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspxis the syntax for creating different types trigger and syntax to use those.
  Sakthi Meenakshi replied to asif hameed
26-Apr-10 02:55 AM


Hi,

I hope that the below SQL query will be very useful for you.

Let us consider the table old which maintains the history of the student record along with the datetime

The table test2  maintains the student record.

Query:

begin

 Insert into old (SSN,Sname,Marksecured,[date]) select SSN,Sname,Marksecured ,GETDATE() from test2

 update  test2 set marksecured = 90 where ssn=1

end

Thanks & Regards
SakthiMeenakshi.S

Create New Account
help
SQL server 2000 SQL Server Does Microsoft still support SQL server 2000? SQL Server New Users Discussions SQL Server (1) SQL Server Books Online (1) SQL server 2000 (1
MSDE on Windows 2003 R2 box, new DL385G6 - Install Fails during SQL Services SQL Server I have been finding that I am having trouble with the Crystal Reports Server XI installation failing when it is dealing with SQL. So, as a thought and in case there was something wrong with my SQL portion of the isntall. I thought ok, I will try installing the actual MSDE application direct from Microsoft. So, I downloaded the MSDE for SQL 2000 (which is msde2000a.exe), set my switches and off to the races. It quit seconds left to the installation and bombed with the same errors as the Crystal Reports Server install. The error is the same whether I try to install MSDE by itself or
strawberry perl and sql server SQL Server I need to connect to sql server from strawberry perl. Is anyone aware of any free driver for this. thanks. SQL Server Discussions SQL Server 2005 (1) SQL Server 2000 (1) SQL Express (1) SQL Server (1
server SQL Server Do I have to set up a sql server database on a sql server? Or can I do this on any server SQL Server Setup Discussions SQL Server (1) CREATE DATABASE (1) Databases (1) Database (1) Create
Conditional compilation [SQL Server] SQL Server Does SQL Server support conditional compilation? - -Sunny SQL Server Discussions SQL Server (1) Preprocessor (1) Sunny.mohan (1) Ifdef (1) Can you define what