Best Practice? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Best Practice?

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-09-20 : 18:09:02

I have a RevisedDate column in several of my tables.

Here are some options for it.

1)
Not Null
Constraint name Default getdate()
Update Trigger

2)
Null
Insert, Update Trigger

I believe the first one is better since it maintains data integrity making sure the column is not null. But do i really need a constraint and a trigger doing basically the same thing? Should i go with option 2 since the same function will be in one location? Or is there another option?

Which do you think is best?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-20 : 18:13:04

Not null, default of GETDATE(), let the stored procedure or application send in GETDATE() on updates.

I try to avoid triggers if at all possible. I typically only use a trigger as a quick fix or when the application is not in development.

Tara

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-20 : 19:47:14

How about not null with no default and let the insert/update stored procedures take care of it?

CODO ERGO SUM

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-09-21 : 09:03:11

Thanks guys for the info!!!!

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-21 : 10:00:03

quote:
Originally posted by tduggan

Not null, default of GETDATE(), let the stored procedure or application send in GETDATE() on updates.

I try to avoid triggers if at all possible. I typically only use a trigger as a quick fix or when the application is not in development.

Tara

And history...you forgot to say "to track historical changes to the data"

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

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-09-21 : 10:46:09

Stored procedures help to track historical changes? But aren't Triggers a type of stored procedure? Why are SPs preferred?

At the moment, I'm using triggers as a quick fix because i dont' know how to write stored procedures yet or even good triggers for that much. When i get finished with this first phase, i plan to rewrite them as suggested.

Thanks guys!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-21 : 12:32:10

quote:
Originally posted by X002548

quote:
Originally posted by tduggan

Not null, default of GETDATE(), let the stored procedure or application send in GETDATE() on updates.

I try to avoid triggers if at all possible. I typically only use a trigger as a quick fix or when the application is not in development.

Tara

And history...you forgot to say "to track historical changes to the data"

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

I don't ever do that. We can't afford the performance hit. If we needed to see what was changed, I guess we'd look into Log Explorer and use as needed.

quote:
Originally posted by JacobPressures

Stored procedures help to track historical changes? But aren't Triggers a type of stored procedure? Why are SPs preferred?

At the moment, I'm using triggers as a quick fix because i dont' know how to write stored procedures yet or even good triggers for that much. When i get finished with this first phase, i plan to rewrite them as suggested.

Thanks guys!

Triggers can be used to copy the data from the inserted and deleted table into historical tables. It's called auditting.

All data access should be through stored procedures.

Here's two simple stored procedures for you to look at. Pretend we have two columns, Column1 int and Column2 datetime. Column2 will be the one with the default of GETDATE().

CREATE PROC usp_Table1_Add
(@var1 int)
AS

INSERT INTO Table1 (Column1) VALUES(@var1)

-- don't need to provide a value for Column2 since we have a default.

GO

CREATE PROC usp_Table1_Upd
(@var1 int, @var2 int)
AS

UPDATE Table1 SET Column1 = @var1, Column2 = GETDATE()
WHERE Column1 = @var2

GO

Tara

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-09-21 : 13:34:07

Thanks! That is very helpful!!!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-21 : 14:40:08

You can do 2 columns, in many cases I find both these items of metadata useful:
RegDate NOT NULL DEFAULT(GETDATE())
RevisedDate NULL -- update trigger

Any clients can forget about their existence.
If You are running mass updates in controlled manner you can disable the update trigger and specifically provide the RevisedDate for performance reasons.

This all depends on the auditing requirements for the table.
What metadata do You need to track times, users ?
Do You need to track all revision history to the record, or just the last revision date ?
Can You rely on all DML being done by the designated sprocs ?
etc...

Kristen
Test

22859 Posts

Posted - 2005-09-21 : 15:11:45

"We can't afford the performance hit [of triggers]"

Very good point. We have triggers on all our tables "policing", and silently "fixing" stuff.

I'm gonna review policy with a view to changing all triggers to RAISERROR instead of "Silently Fix" errors, and then ditch them in production. All our Sprocs are supposed to be doing everything that the triggers are ...

... except Auditing, but the Sprocs could do that too.

Kristen

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-09-21 : 16:56:38

I have not decided how i am going to do auditing. I'm a newbie and I have read about some approaches and their draw backs. I kind of like the idea of creating a table that saves all the changes that were made, but then i would have to figure out how to reverse it. Right now I'm using logical deletes instead of deletes as a safety measure. Or at least i'm planing to. I'm not finish with it yet.

I do want to be able to build such implementation and would like some ideas. But right now this DB doesn't demain tracking all changes to the data.

Kristen
Test

22859 Posts

Posted - 2005-09-21 : 17:07:20

We have a link on our "record maintenance screens" to display the Audit History [in a grid]. User's can cut&paste data from that if they want to re-introduce some older data.

Given relationships between tables its hard to create a simple "undo" button, so IMO its easier to give users the "raw" data and let them sort it out for themselves - at least the data is available to them to use as a basis for repairing unintended changes, which wouldn't be the case if there was no Audit.

The other use of an Audit is after-the-fact - such as fraud, or even just a plain accident. Easy to report on, or analyse, if the need arises.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources