|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Want to automatically record creat and updated date in rows... are triggers the way to go?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Want to automatically record creat and updated date in rows... are triggers the way to go?
|
|
| Bill Mattox 2002-11-09, 12:23 pm |
| I want to automatically record the insert and updated datetime for rows. I
know I can use a default or stored procedure, but am thinking that triggers
might be a better way to go.
Does anyone have any opinion's on the best approach for this task?
| |
| Dan Guzman 2002-11-09, 1:23 pm |
| There are pros and cons of using triggers. The advantage is that the
datetime can be recorded for both inserts and updates regardless of the
method used to insert/update data. However, triggers are less efficient
than populating the datetime value from a default constraint or stored
procedure value.
A default constraint applies only to inserts and can be overridden with
an explicit value on the insert.
Stored procedures are an efficient method and may be the best choice if
inserts and updates are done exclusively by procedures.
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Bill Mattox" <gwmattox_jr41---nospam---@hotmail.com> wrote in message
news:uC9QWTBiCHA.1336@tkmsftngp11...
> I want to automatically record the insert and updated datetime for
rows. I
> know I can use a default or stored procedure, but am thinking that
triggers
> might be a better way to go.
>
> Does anyone have any opinion's on the best approach for this task?
>
>
| |
| Jasper Smith 2002-11-09, 1:23 pm |
| If all access to the table(s) is via stored procedures (and if not
why not) then use the stored procedure. Triggers just add more
complexity and are yet another bit of code you need to maintain.
I'm not a big fan of triggers and tend to be of the opinion that
all access should be via stored procedures and users/applications
have no explicit permissions on tables other than via stored procedures.
--
HTH
Jasper Smith (SQL Server MVP)
Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm
"Bill Mattox" <gwmattox_jr41---nospam---@hotmail.com> wrote in message
news:uC9QWTBiCHA.1336@tkmsftngp11...
> I want to automatically record the insert and updated datetime for rows. I
> know I can use a default or stored procedure, but am thinking that
triggers
> might be a better way to go.
>
> Does anyone have any opinion's on the best approach for this task?
>
>
| |
| Ricky Artigas 2002-11-10, 12:23 am |
| Triggers allows you to control the data that will be put on the datetime
column. This way, people won't be able to override or cheat the datetime
value and put a value that is not the current date and time (that is if you
need to track the current date and time) as opposed to using stored
procedures or sql statements to insert the datetime value. It actually
depends on your requirement.
If you need it for something like tracking purposes as to when a record was
created or updated, then triggers would be better.
"Bill Mattox" <gwmattox_jr41---nospam---@hotmail.com> wrote in message
news:uC9QWTBiCHA.1336@tkmsftngp11...
> I want to automatically record the insert and updated datetime for rows. I
> know I can use a default or stored procedure, but am thinking that
triggers
> might be a better way to go.
>
> Does anyone have any opinion's on the best approach for this task?
>
>
| |
| Jasper Smith 2002-11-10, 9:23 am |
| However in most applications the create and update datetime
of the record would not be exposed to the application/user so
regardless, they would not be able to affect it.
--
HTH
Jasper Smith (SQL Server MVP)
Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm
"Ricky Artigas" <ricky_artigas@yahoo.com> wrote in message
news:#YVaxCIiCHA.2456@tkmsftngp12...
> Triggers allows you to control the data that will be put on the datetime
> column. This way, people won't be able to override or cheat the datetime
> value and put a value that is not the current date and time (that is if
you
> need to track the current date and time) as opposed to using stored
> procedures or sql statements to insert the datetime value. It actually
> depends on your requirement.
> If you need it for something like tracking purposes as to when a record
was
> created or updated, then triggers would be better.
>
> "Bill Mattox" <gwmattox_jr41---nospam---@hotmail.com> wrote in message
> news:uC9QWTBiCHA.1336@tkmsftngp11...
> > I want to automatically record the insert and updated datetime for rows.
I
> > know I can use a default or stored procedure, but am thinking that
> triggers
> > might be a better way to go.
> >
> > Does anyone have any opinion's on the best approach for this task?
> >
> >
>
>
| |
| Bill Mattox 2002-11-10, 11:23 am |
| One of the reasons I am leaning toward this is that the .NET SqlDataAdapter
can automatically generate the CRUD SP's that use a form of optimistic
locking but do not have the granularity to add custom logic for audit
purposes without editing them afterward. I know I can do this but,
especially during the development phase where changes are occuring and I am
regenerating the SP's, it seemed a little easier to use triggers for the
audit portion, which will be common to all tables and will not change.
Another reason for having a better protected updated date is for use as a
high-water mark in synching distributed ADO.NET DataSets that use a GUID for
unique id rather than an identity column, which is not practical in a highly
distributed architecture.
"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
news:O2vL9uMiCHA.2256@tkmsftngp12...
> However in most applications the create and update datetime
> of the record would not be exposed to the application/user so
> regardless, they would not be able to affect it.
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
>
> Check out the PASS Community Summit - Seattle, the largest and only user
> event entirely dedicated to SQL Server, November 19-22.
> http://www.sqlpass.org/events/seattle/index.cfm
>
> "Ricky Artigas" <ricky_artigas@yahoo.com> wrote in message
> news:#YVaxCIiCHA.2456@tkmsftngp12...
> > Triggers allows you to control the data that will be put on the datetime
> > column. This way, people won't be able to override or cheat the datetime
> > value and put a value that is not the current date and time (that is if
> you
> > need to track the current date and time) as opposed to using stored
> > procedures or sql statements to insert the datetime value. It actually
> > depends on your requirement.
> > If you need it for something like tracking purposes as to when a record
> was
> > created or updated, then triggers would be better.
> >
> > "Bill Mattox" <gwmattox_jr41---nospam---@hotmail.com> wrote in message
> > news:uC9QWTBiCHA.1336@tkmsftngp11...
> > > I want to automatically record the insert and updated datetime for
rows.
> I
> > > know I can use a default or stored procedure, but am thinking that
> > triggers
> > > might be a better way to go.
> > >
> > > Does anyone have any opinion's on the best approach for this task?
> > >
> > >
> >
> >
>
>
|
|
|
|
|