SQL Triggers - 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

SQL Triggers

Author

Topic

M_BSQL
Starting Member

8 Posts

Posted - 2008-01-10 : 06:21:35

Hi

I'm trying to detect whether a particular field has been updated and I think triggers may be the way forward but I'm not sure.

Basically I have a table of users with various bits of info and one of the fields is called endDate. Basically I will have a asp.net application that runs nightly and I need to be able to detect when the endDate field has been updated for any of the users in the table.

My question is, are triggers the best way to do this?

Thanks in advance for any replies!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 06:33:53

what values will the asp.net application be populating the end date field?

M_BSQL
Starting Member

8 Posts

Posted - 2008-01-10 : 06:41:30

Basically the application will detect whether the endDate field field has been updated for any of the users and then will write info about that user (ID etc) to a text file.

Can triggers help me with this??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 06:52:43

If i understand you correctly, you dont require a trigger here. You just need a default constraint on enddate to auto populate current date. So whatever records were updated on a day will have that days date as end date.
The asp.net appln can check for records with that days date ( or previous days date) and do rest of processing. The check condition depends on when appln is scheduled to run. If its before 12 midnight then check for current date or check for current date -1 to get updated records.

M_BSQL
Starting Member

8 Posts

Posted - 2008-01-10 : 07:02:20

Thanks for the reply, I'll have a look at constraints!!

M_BSQL
Starting Member

8 Posts

Posted - 2008-01-10 : 09:01:51

quote:
Originally posted by M_BSQL

Basically the application will detect whether the endDate field field has been updated for any of the users and then will write info about that user (ID etc) to a text file.

Can triggers help me with this??

Hi I really am a novice when it comes to this could you point me in the right direction please?

M_BSQL
Starting Member

8 Posts

Posted - 2008-01-10 : 09:02:32

quote:
Originally posted by visakh16

If i understand you correctly, you dont require a trigger here. You just need a default constraint on enddate to auto populate current date. So whatever records were updated on a day will have that days date as end date.
The asp.net appln can check for records with that days date ( or previous days date) and do rest of processing. The check condition depends on when appln is scheduled to run. If its before 12 midnight then check for current date or check for current date -1 to get updated records.

Hi I really am a novice when it comes to this could you point me in the right direction please?

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-10 : 09:25:13

Yes you want a trigger

I do this for usually all of my tables

I create a mirror of the base table

Upon an update or delete I have a trigger that would fire and insert the previous row to the history table

I add extra columns to tell me what the dml operation was, what time it happened, the machine name, and what system user did it

Need code?

Post the DDL of the table you're working on

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

Add yourself!
http://www.frappr.com/sqlteam

M_BSQL
Starting Member

8 Posts

Posted - 2008-01-10 : 09:41:10

quote:
Originally posted by X002548

Yes you want a trigger

I do this for usually all of my tables

I create a mirror of the base table

Upon an update or delete I have a trigger that would fire and insert the previous row to the history table

I add extra columns to tell me what the dml operation was, what time it happened, the machine name, and what system user did it

Need code?

Post the DDL of the table you're working on

Brett

8-)

Sorry to sound like even more of a novice but what do you want me to post??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources