Tips on Using SQL Triggers

Solomon Cloud Solutions brings you tips for creating triggers to help you manage your Microsoft Dynamics SL data more efficiently.

A trigger is a SQL command that is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
​
While triggers can be used for history and audit purposes, as well as, to correct data that should be populated, or to fix data that was entered incorrectly in this case our trigger example is to update a blank field that should be populated.  In the example the trigger was created to update a Microsoft Dynamics SL customer’s data where the CuryID (currency ID) field is appearing as blank, when a new record is being inserted into that table, but needs to be corrected to reflect the system’s base currency. By using the following trigger SQL is able to quickly evaluate the data, locate any blank CuryID fields and replace with the systems base currency:
 
create trigger [dbo].[xCuryPurchord] on [dbo].[PurchOrd]
for insert as
set nocount on
if (select COUNT(*) from inserted where curyid='')>0
begin
                update x set x.curyid='USD'
                                from Purchord x inner join inserted i on i.PONbr=x.PONbr
                                where i.CuryID=''
                                               
end
 
Stay tuned for more hints and tips from Solomon Cloud Solutions on the Microsoft Dynamics SL product, and more.
_____________________________________________________________________________________
Solomon Cloud Solutions is a software publisher and consulting organization that provides development and consulting services to partners. Solomon Cloud Solutions was formed specifically to develop a complete cloud platform to accelerate development and deployment of their cloud based business solutions across their family of companies and their business partners. The Solomon Cloud Solutions companies operate under the brand names of Six Disciplines - publisher of performance management software, methodology and coaching and Beyond Software - publisher of project accounting software and are owned by Gary Harpst, Vernon Strong and Jack Ridge.