April 2, 2008 at 9:57 pm
I have a few things I'm trying to accomplish here.
I have 2 tables
USERS and ATTENDANCE
I want to set something up such that when a record is 'added' to the users table that it inserts data for that user in to the attendance table. I believe this would be done via a trigger but can't seem to figure it out. Note: I am working in the 2005 IDE so if it would be easier to do this via TSQL, i'm happen to do that too.
Secondly, I want to have something kick off automatically each night which will take ALL users (UID) in the users table and insert them in to the attendance table. One of the columns in the attendance table is DATE which has a default value of GetDate(), so basically I will end up with one entry, per user defined in the users table, for every day.
I think I have the query to insert all users from the USERS table in to the attendance table by using ...
insert into ATTENDANCE (UID)
select UID from USERS
This seems to work since 4 of the 5 columns in my attendance table have default values.
So, I'm guessing that I can use thsi query as the items that runs nightly to input 'that days' default values in to the table for all defined users? If so, can someone direct me to a sample of how to setup a trigger (provided that is the best method for handling this)
The next piece I'm stumbling with is when a new user is added to the DB. When this occurs, I want just the newly added UID in the users table to be inserted in to the ATTENDANCE table. I guess I'm missing how I can insert just the 'newly created' record in to the table and not ALL of them like done above.
LASTLY (sorry for the long post)
How difficult would it be to build something in SQL such that an automated process can check a collection of records in a table to see if the last 'x' number contain 0's. If so, modify a different column.
Basically, I'm trying to track 14 days worth of data and if colB for UID 2 is 0 for the past 14 days... do something
Any help with this stuff would be GREATLY appreciated.
Thank you much!
Bob
April 17, 2008 at 1:25 pm
1) Create a trigger. See Books On Line - it is very easy to follow
2) Create a stored procedure and have it run as a SQL Server Agent job each night.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply