Wednesday, June 5, 2013

[SQL Server] Trigger with calculations

[SQL Server] Trigger with calculations

Trigger with calculations

Posted: 05 Jun 2013 07:06 AM PDT

I have to write a trigger that stores old values in an audit table, I have managed to write the trigger and it updates the table with the old value, I am happy so far, except I have to populate the table with the difference in the update and not the old value (ie) sum of the salaries after the update - sum of salaries before update,Assuming my update query updates 3 rows, each by 100, I need to include in my trigger code that will subtract the old value (which I have found) from the new value. My problem now is I have no idea how to go about finding the new value! Because I am updating more than one row in the employee table and not saving the new value to the audit table but I need to know it so I can calculate against it.Here is my current code[code="sql"]CREATE TRIGGER Update_Salaries ON EMPLOYEE AFTER UPDATEAS BEGIN IF (COLUMNS_UPDATED() & 14) > 0 BEGIN INSERT INTO AUDIT ( notes,delta) VALUES ('Salaries Updated',(SELECT SUM(salary) FROM deleted)) ENDENDGO[/code]Any ideas would be greatly appreciated.

Need some help with some SQL Queries

Posted: 05 Jun 2013 12:33 AM PDT

I'm struggling with following issues that I would like to see if it can be resolved using SQL QueriesFollowing is the data (only sample data)resourceID Name0 VariableValue016777220 WKSRELEASE 216777220 WKSMAINT 6 SCV2016777220 WKSOS W7_64like to show the data as followresourceID internal version16777220 W7_64 2.6 SCV20Thx

Database mail

Posted: 04 Jun 2013 11:04 PM PDT

(Bear with me, I have Googled this first, and done a search here! I've followed the Troubleshooting Database Mail tips, and it's working fine on all of my databases except one, and I'm not sure why.)I want my SQL Server Agent jobs (specifically backups) to notify me when they fail (duh), and to test it I've set one to notify me on Success. Then I'll set it to Notify on Failure once I'm happy it's working. However, when I run the job (just backing up the log file) and wait for an email confirmation, I get nothing.On the job properties under Notifications, Email is ticked, I select my operator from the drop-down list, and 'when the job succeeds' is selected.Under Operators, <my operator>, Properties, Enabled is ticked, the email address is correct. Under Notifications it says 'On success', but under History it says 'Never emailed'.I did restart SQL Server Agent..A test email DOES work.Any ideas please? I've compared my settings to my other servers (which do work fine) and they match, but I must be missing something stupid. Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog