how to write trigger

Question

Post reply

how to write trigger

Muhammed Sajid Hussain

SSC Veteran

Points: 290
More actions
March 20, 2006 at 9:21 am

#67881

Hi,
Kindly can you help me for the follownig question...
Whenever a user inserts a record into database(sqlserver), i want to write one trigger such that it fires on a particular date(suppose on sunday) and all the records which have been inserted from monday to saturday should be updated to the server on sunday when the trigger fires.
Please suggest me
Thanks in advance
Best Regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 1

Okay, take the word trigger out and explain again. Triggers are fired upon an action, not a date.

Is it that you want inserts for the week held back until sunday?

Muhammed Sajid Hussain SSC Veteran Points: 290 More actions · Answer 2

Hi Joseph,

Thanks for guidence...

You are 100% right.

Best Regards

John Laska SSC-Addicted Points: 454 More actions · Answer 3

You'll need to do a 2 step process. And you have a couple of options, you'll want to research which will work best for you.

Create a trigger on the table which updates some sort of auditing table. e.g.:

<code>

CREATE TRIGGER MyTrigger ON MyTable FOR AFTER INSERT
AS
  INSERT INTO 
    MyAuditTable (RecordID, Date) 
  VALUES 
    (INSERTED.RecordID, getdate())

</code>

Then, have a job set to run every Sunday which updates all of the records as required.

UPDATE MySundayTable
  SET (Some Columns) = SomeValues, 
       ...n
  WHERE RecordId in (SELECT RecordID FROM MyAuditTable)
Do Other Stuff Here
  TRUNCATE MyAuditTable

</psudeoCode>

The problem with this approach is that it has a processing cost every time a record is inserted, so if you have a high transaction environment you are going to slow things down a touch.

</Option1>

Extend the main table to include an InsertedDate column. Modify the application to always include this value when it inserts records. Then, just create a job which trolls the main table every Sunday and does stuff as required.

UPDATE MySundayTable

SET (Some Columns) = SomeValues,

...n

WHERE RecordId in (SELECT RecordID FROM MyTable WHERE InsertedDate > DATEADD(dd, -7, GETDATE()))

Do Other Stuff Here

</psudeoCode>

Obviously this option requires that you are allowed to modify tables and the application(s), but the processing cost per transaction should be lower, just make sure you index the InsertedDate column, or the Sunday job is going to be doing table scans, which are not fun on large tables.

</Option2>