Databases
StackOverflow2013
Postsdbo
POTrigger doesn't work as expected
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrigger doesn't work as expected
    text
    copied!<p>I'm having a bit of a trouble with my Trigger. It's supposed to:</p> <ul> <li>Update a history table on inserts in my primary table and add a timestamp.</li> <li>If a row in the history table already has the same values, nothing should be added, but a counter should increase and the time should be updated.</li> <li>If it was over 24hours ago since last update, a new row will be created.</li> </ul> <p><strong>Sloppy pseudocode:</strong></p> <pre><code>IF NOT EXISTS ( --if the value isn't in the history table SELECT History.value1 FROM History, INSERTED WHERE History.value1 LIKE INSERTED.value1 AND History.value2 LIKE INSERTED.value2 ) OR EXISTS ( --or if it has been added over 24h ago SELECT History.value1 FROM History, INSERTED WHERE History.value1 LIKE INSERTED.value1 AND History.value2 LIKE INSERTED.value2 AND DATEDIFF(HOUR,History.time, GETDATE()) &gt; 24 ) BEGIN --Insert it INSERT INTO History(value1, value2, counter, time) SELECT value1, value2, counter GETDATE() FROM INSERTED END ELSE BEGIN -- else, increase counter and add new time UPDATE History SET History.time = GETDATE(), History.Items = History.Items + INSERTED.Items FROM History JOIN INSERTED ON History.value1 = INSERTED.value1 AND History.value2 = INSERTED.value2 AND DATEDIFF(HOUR, _History.time, GETDATE()) &lt; 24; END </code></pre> <p><strong>example table:</strong></p> <pre><code>__________________________________________________ | value1 | value2 | counter | time(last updated) | +------------------------------------------------+ | test1 | test2 | 1 | &lt; 24h | | test3 | test4 | 1 | &gt; 24h | | test3 | test4 | 1 | &lt; 24h | +------------------------------------------------+ </code></pre> <p><strong>input:</strong> </p> <pre><code>INSERT INTO main_table(value1, value2, counter) VALUES ('test3', 'test4', 1); </code></pre> <p><strong>resulting table:</strong></p> <pre><code>__________________________________________________ | value1 | value2 | counter | time(last updated) | +------------------------------------------------+ | test1 | test2 | 1 | &lt; 24h | | test3 | test4 | 1 | &gt; 24h | | test3 | test4 | 1 | &lt; 24h | &lt;--This counter+time should be updated | test3 | test4 | 1 | &lt; 24h | &lt;--This row shouldn't be added +------------------------------------------------+ </code></pre> <p>I Understand WHY this happens (because the code finds a history value thats over 24h, disregarding the newer one(s)) but I dont know how to fix it.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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