stored procedures - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

stored procedures

Author

Topic

Diddah
Starting Member

6 Posts

Posted - 2011-11-09 : 14:36:43

HI guys, hope you all well. Am new in sql and have to do this project where I create stored procedure to get changes from table(s) and store the changes in a table that have created. Please help where to start....

Dan Dan

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 16:03:08

can you post the actual BRD..or the homework assignment

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Diddah
Starting Member

6 Posts

Posted - 2011-11-09 : 16:20:18

thanx brett for the links...basically its a task that am thinking of and I still do not where to start...the table where i want the changes to be stored is

CREATE TABLE [dbo].[Table_FirstResponders_LogTable](
[Change_LogID] [int] IDENTITY(1,1) NOT NULL,
[Change_TableName] [nvarchar](150) NULL,
[Change_FieldName] [nvarchar](150) NULL,
[FieldData_Original] [nvarchar](150) NULL,
[FieldData_Changed] [nvarchar](150) NULL,
[Change_Date_Time] [datetime] NULL,
[User_Change] [nvarchar](150) NULL,
[Updated_Changes] [int] NULL,
CONSTRAINT [PK_Table_FirstResponders_LogTable] PRIMARY KEY CLUSTERED
(
[Change_LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and the other tables is :

CREATE TABLE [dbo].[Table_FirstResponders](
[Responder_ID] [int] IDENTITY(1,1) NOT NULL,
[Organization_Name] [nvarchar](150) NULL,
[Organization_Name_Legal] [nvarchar](150) NULL,
[Organization_Desc] [nvarchar](150) NULL,
[Priority] [bit] NULL,
CONSTRAINT [PK_Table_FirstResponders] PRIMARY KEY CLUSTERED
(
[Responder_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

do not know if this helps......

Dan Dan

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 16:23:45

First, I hate Identity has a PK...unless you have an Alternate Key, you could just get garbage...

If you want to track data changes I would suggest a trigger. Do you know what a trigger is?

Or am I missing the point?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Diddah
Starting Member

6 Posts

Posted - 2011-11-09 : 17:01:29

yeah thats what I was looking at, still in the process of learning it too.....no u are not missing the point. I understand.
Is it possible when the trigger gets the data I can tell it to store in a table.... btw thanks for the advice

Dan Dan

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-09 : 17:16:03

Don't store changes for multiple tables in one big table.

Make a log table for each one you want to audit, then create a trigger to populate it.

And your IDENTITY field in the FirstResponders table is appropriate. Can't think of any reason to use one in the log table though.

I will share one of my pet peeves though...I hate prefixes on tables. We already know it's a table. So why prefix it with "Table_"? Just adds clutter -- especially when searching through long lists of tables.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-09 : 17:23:59

Something like this is what I do

CREATE TABLE [dbo].[FirstResponders_Log](
	[Responder_ID] [int] NOT NULL,
	[Organization_Name] [nvarchar](150) NULL,
	[Organization_Name_Legal] [nvarchar](150) NULL,
	[Organization_Desc] [nvarchar](150) NULL,
	[Priority] [bit] NULL,
	[date] datetime not null,
	[who] sysname,
	[host] varchar(128)
) ON [PRIMARY];
GO

CREATE TRIGGER trgFirstResponders On Table_FirstResponders
FOR UPDATE, DELETE
AS
INSERT	FirstResponders_Log (
	Responder_ID, Organization_Name, Organization_Name_Legal, Organization_Desc,
	Priority, [date], who, host
)
SELECT	Responder_ID, Organization_Name, Organization_Name_Legal, Organization_Desc,
	Priority, GetDate(), SYSTEM_USER, host_name()
FROM	DELETED;
GO

Diddah
Starting Member

6 Posts

Posted - 2011-11-09 : 17:24:11

this was a sample DB I was provided with to try out my task but will change that...thanks for the heads up....can triggers capture the old and new data and store it....and was just curious to know if we could store also the action (delete, insert, or update)
btw ur code man you the best....it worked like a charm....how could it also learn this great stuff....

Dan Dan

:-)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-09 : 17:53:13

Stick around, that's how

Also read through BOL a lot. And practice, practice, practice.

Hope to see more from you around here!

Diddah
Starting Member

6 Posts

Posted - 2011-11-09 : 17:56:00

.and was just curious to know if we could store also the action (delete, insert, or update)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-09 : 18:16:37

You could, by using multiple triggers.

I won't audit inserts, just deletes and updates. Then I can compare my log table to the live table to see what the differences are when necessary.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:12:16

The BASE table wiull hold the NEW data..no need to store it twice..updated or deleted data goes to History

Here

http://weblogs.sqlteam.com/brettk/archive/2006/11/29/35816.aspx

And you'll need history tables

http://weblogs.sqlteam.com/brettk/archive/2006/08/10/11126.aspx

Let us know if you need any more help

It's 5:00 somewhere

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources