Databases
StackOverflow2013
Postsdbo
POUpdate Trigger better approach
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate Trigger better approach
    primarykey
    data
    text
    <p>I have a Trigger i am developing and want it to basically handle Updates in the sense of the <code>Update</code> statement, not so worried about <code>Delete</code> or <code>Insert</code> statements at the moment.</p> <p>The end result is that i have two status fields <code>active</code> &amp; <code>inactive</code>, which are <code>bit</code> &amp; <code>datetime</code> respectively. The <code>active</code> field is used to invalidate a record, manually for the time, and i want to make sure that the <code>inactive</code> field contains a value of <code>active</code> change.</p> <p>Ideally, i would like to check to see if <code>active=0</code> and place a datetime stamp, using <code>now()</code>, in the field for the record. Long-term im sure i will want to validate that the <code>inactive</code> field doesnt have a datetime stamp already, but for now overriding it fine. As well, would like to have it check if <code>active=1</code> and clear out the <code>inactive</code> field if any value exists.</p> <p>The <code>inactive</code> field is <code>nullable</code> so there wont be a conflict.</p> <h3>Trigger Definition, as of now:</h3> <pre><code>CREATE TRIGGER dbo.TRG_tblRegistration_Inactive ON dbo.tblRegistration AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF update(active) BEGIN update r set inactive=now() from tblRegistration r join inserted i on r.id = i.id where i.active = 0 END END </code></pre> <p>I have a pretty good understanding of the <code>inserted</code> &amp; <code>deleted</code> trigger tables.</p> <h3>Question:</h3> <ol> <li>Is there a better logic pattern within the trigger that will get me the ultimate desired goal?</li> <li>Any considerations to be had if they submit a <code>inactive</code> value with the <code>active=0</code>, or <code>active=1</code>?</li> </ol> <h3>Current Form of the Trigger:</h3> <pre><code>ALTER TRIGGER dbo.TRG_tblRegistration_Inactive ON dbo.tblRegistration AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF update(active) BEGIN update r set inactive=getdate() from tblRegistration r join inserted i on r.id = i.id where i.active = 0 update r set inactive=NULL from tblRegistration r join inserted i on r.id = i.id where i.active=1 END END </code></pre> <p>It works and does as expected but want to make sure that first there isnt a better way of doing this (at the database side or in the trigger).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USGoldBishop
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou can also just use the inactive flag. Instead of updating active=0, just update inactive=now() instead. That way you can get rid of the trigger altogether and the active flag. Not sure if that's an option for you however.
      singulars
      1. POUpdate Trigger better approach
      1. USsam yi
    2. COI personally do not like triggers too much.... I avoid them whenever possible.
      singulars
      1. POUpdate Trigger better approach
      1. USsam yi
    3. CO@samyi i wasnt going to change the value of `active` just the `inactive` value. I am only doing this cause the product i am developing needs to be supportive of the local IT. I am doing the trigger as a safe guard against IT rollover
      singulars
      1. POUpdate Trigger better approach
      1. USGoldBishop
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload