Some cool NMMI SQL Triggers

1. Some cool NMMI SQL Triggers by Bryan Yates, bryan@nmmi.edu New Mexico Military Institute in Roswell, NMhttp://WordPress.nmmi.edu/ComputerTraining My Info

2. In the beginning • We migrated to ABT PowerCAMPUS in 2002 • We realized ABT did a lot of things in triggers and we could also leverage this for a lot of business processes • We decided that upgrades were too risky to create our own tables in Campus6, so we created a new Database called Institute. • We initially didn’t know we could have multiple insert/update/delete triggers per table, so we initially called a stored procedure called from ABT’s trigger and re-inserted it after every upgrade • We check to ensure we’re in the live database before pushing data to Institute, this could be improved if all tables were in the Campus6 database. Testing with db copies would be better.

3. Our first audit trigger • -- Use the Specify Values for Template Parameters • -- command (Ctrl-Shift-M) to fill in the parameter • -- values below. • --<TABLETOALTER,,> • --<UNIQCOL,,id> • --<DATABASE,,institute> • use <DATABASE,,institute> • ALTER TABLE <TABLETOALTER,,> ADD create_datedatetime NULL • , create_timedatetimeNULL, create_uservarchar(12) NULL, revision_datedatetimeNULL • , revision_timedatetimeNULL, revision_uservarchar(12) NULL • go • CREATE TRIGGER ti_audit_<TABLETOALTER,,> ON <TABLETOALTER,,> • FOR INSERT • AS • /* Audit Fields */ • declare @I_Idvarchar(20) • select @I_Id = <UNIQCOL,,id> from Inserted • exec sp_Audit_Create '<TABLETOALTER,,>','<UNIQCOL,,id>',@I_Id • go • CREATE TRIGGER tu_audit_<TABLETOALTER,,> ON dbo.<TABLETOALTER,,> • FOR UPDATE • AS • /* Audit fields */ • declare @I_Idvarchar(20) • select @I_Id = <UNIQCOL,,id> from Inserted • exec sp_Audit_Revision '<TABLETOALTER,,>','<UNIQCOL,,id>',@I_Id • go

4. Lessons learned • Only works on first item if bulk insert done • Insert into tbl (id,name) values (1,’a’),(2,’b’) • Inset into tbl (id,name) select top 2 id,name from tbl2 • Templates are cool, sometimes • Only usable on tables with a single primary key • Should have used NMMI in the name of the trigger, and a description, like ti_TBL_NMMI_audit • Eventually learned that you can make triggers for insert AND update in the same trigger

5. New database table, who did that? • -- ============================================= • -- Author: Gary Hill • -- Create date: 6/6/2013 • -- Description: Send an e-mail to programmers when a new database is created. • -- ============================================= • CREATE TRIGGER [trg_DDL_CreateDatabase] • ON ALL SERVER • FOR CREATE_DATABASE • AS • declare @results varchar(4000), @subjectTextvarchar(200) • SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() • SET @results = • (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) • EXEC msdb.dbo.sp_send_dbmail • @profile_name = 'DBmailprofile', • @recipients = 'programmers@nmmi.edu', • @body = @results, • @subject = @subjectText • GO

6. New database table, the email

7. Database Mail

8. View existing Email profiles • exec msdb.dbo.sysmail_help_account_sp

9. tu_residency_nmmi, room changes • Simple enough, let’s track some room changes, but not just between semesters • Originally used to help keep the phone number with the student • Extended to keep the switch and port info stored in FAX field to know if student port was locked (loss of privilege). • Abandoned by IT due to in room phones going away and edge smart switch management improvements • Housing office still uses to review problem cases

10. tu_residency_nmmi • if (isnull(@D_DORM_ROOM,'')<>isnull(@I_DORM_ROOM,'')) -- only the guts are shown • AND (@I_ACADEMIC_SESSION='') • AND (DB_NAME() = 'CAMPUS6') • exec Institute.dbo.sp_room_change • @I_PEOPLE_ID, • @D_DORM_ROOM, • @I_DORM_ROOM, • @I_MAIL_SLOT, • @I_ACADEMIC_YEAR, • @I_ACADEMIC_TERM, • @I_REVISION_DATE, • @I_REVISION_TIME, • @I_REVISION_OPID, • @I_REVISION_TERMINAL • -- BAY 7/22/09 to allow room/troop/status updates to go straight into Housing • if @I_ACADEMIC_SESSION='' • AND DB_NAME() = 'CAMPUS6' • AND (isnull(@D_DORM_ROOM,'') <> isnull(@I_DORM_ROOM,'') • or isnull(@D_DORM_Plan,'') <> isnull(@I_DORM_Plan,'') • or isnull(@D_Food_Plan,'') <> isnull(@I_Food_Plan,'') • ) • AND @I_PEOPLE_CODE_ID IN (select PEOPLE_CODE_ID from institute.dbo.CMDT_Housing_INFO) • update institute.dbo.CMDT_Housing_INFO set • Room_Id = @I_Dorm_Room • ,Troop = @I_Dorm_Plan • ,Status = @I_Food_Plan • where people_code_id = @I_PEOPLE_CODE_ID

11. sp_room_change • INSERT INTO Institute.dbo.Room_Changes (People_ID, Old_Room_ID, Old_Phone_Number, New_Room_ID, New_Phone_Number, Academic_Year, Academic_Term, Create_Date, Create_Time, Create_User, Create_Terminal, Revision_Date, Revision_Time, Revision_User, Revision_Terminal) • VALUES • (@people_id, @old_room_id, 'n/a', -- @old_phone_number • @new_room_id, 'n/a',@academic_year, @academic_term, @create_date, @create_time, @create_opid, @create_terminal, @create_date, @create_time, @create_opid, @create_terminal) • -- update campus address with new phone number and mail slot • update CAMPUS6.dbo.AddressSchedule • Set Address_Line_1 = 'NMMI Mail Slot '+@mail_slot • from CAMPUS6.dbo.AddressSchedule • where People_org_code_id='P'+@people_id • and Address_type = 'CAMP' • and Status = 'A' • update campus6.dbo.RESIDENCY • set DORM_ROOM = @new_room_id • from campus6.dbo.RESIDENCY RES • join semesterinfo SI • on SI.academic_year = res.ACADEMIC_YEAR • and si.academic_term = res.ACADEMIC_TERM • and si.academic_session = res.ACADEMIC_SESSION • and si.offset > 0 • where res.PEOPLE_CODE_ID = 'P' + @people_id

12. StudentFinancial • This Campus6 table gets created on the roll and on certain events during in-processing • We use the PaymentPlan field to define the “Financial Advisor” for each student. • This is usually based on the first letter of their last name, but full or partial AGA scholarship recipients, and some other exceptions exist • Business Office didn’t have rights to StudentFinancial table because Registrar wanted full control over Tuition_Plan and Tuition_Exemption .

13. StudentFinancialtrigger • CREATE trigger [dbo].[ti_studentfinancialNMMIfinAdv] on [dbo].[STUDENTFINANCIAL] • for INSERT • as • /*********************************************************************** • Description: • Update non-AGA Financial Advisors using the PaymentPlan field • Parameters: • History: • 1/30/2012 BAY created to help auto-maintain Financial Advisors • ************************************************************************/ • update studentfinancial set • PAYMENT_PLAN = FA.PaymentPlanCode • from studentfinancial SF • join inserted INS • on INS.people_code_id = SF.people_code_id • and INS.academic_year = SF.academic_year • and INS.academic_term = SF.academic_term • and INS.academic_session = SF.academic_session • join PEOPLE PEO • on PEO.PEOPLE_CODE_ID = SF.PEOPLE_CODE_ID • join Institute.dbo.BO_STMT_Financial_Advisor FA • on charindex(LEFT(PEO.LAST_NAME,1),FA.FIRST_LETTER) > 0 • where SF.ACADEMIC_SESSION = '01' • and isnull(SF.TUITION_EXEMPTION,'') not in ('aga','aganon') • and isnull(SF.PAYMENT_PLAN,'') = '' • GO

14. StudentFinancial, Supporting data select * from Institute.dbo.BO_STMT_Financial_Advisor -- drives triggers, and pre/billing groupings select * from campus6.dbo.CODE_PAYMENTPLAN -- shows in PowerCAMPUS

15. StudentFinancial summary • I think this trigger is so cool, because of 2 things • The trigger joins directly to the Inserted table, meaning any bulk imports are handled without “looping” over them • The first initial of the last name join is really slick

16. Absent 2 or more times in one day? CREATE TRIGGER [dbo].[ti_tranattendance_nmmi] ON [dbo].[TRANATTENDANCE] FOR INSERT AS DECLARE @I_PEOPLE_CODE nvarchar(1) DECLARE @I_PEOPLE_ID nvarchar(9) DECLARE @I_PEOPLE_CODE_ID nvarchar(10) DECLARE @I_ACADEMIC_YEAR nvarchar(4) DECLARE @I_ACADEMIC_TERM nvarchar(10) DECLARE @I_ACADEMIC_SESSION nvarchar(10) DECLARE @I_EVENT_ID nvarchar(15) DECLARE @I_EVENT_SUB_TYPE nvarchar(4) DECLARE @I_SECTION nvarchar(4) DECLARE @I_ATTENDANCE_DATE datetime DECLARE @I_ATTENDANCE_STATUS nvarchar(10) DECLARE @I_CREATE_DATE datetime DECLARE @I_CREATE_TIME datetime DECLARE @I_CREATE_OPID nvarchar(8) DECLARE @I_CREATE_TERMINAL nvarchar(4) DECLARE @I_REVISION_DATE datetime DECLARE @I_REVISION_TIME datetime DECLARE @I_REVISION_OPID nvarchar(8) DECLARE @I_REVISION_TERMINAL nvarchar(4) DECLARE @I_ABT_JOIN nvarchar(1) DECLARE @I_COMMENTS nvarchar(max) DECLARE @ROWS INT DECLARE @TranAttendanceIdint SELECT @ROWS = @@ROWCOUNT -- this will loop over multiple line inserts correctly If @Rows <= 0 Return Set @TranAttendanceId = 0 Select @TranAttendanceId = (Select Min([TranAttendanceId]) From inserted Where [TranAttendanceId] > @TranAttendanceId) While @TranAttendanceId Is Not Null BEGIN Select @I_PEOPLE_CODE= PEOPLE_CODE, @I_PEOPLE_ID= PEOPLE_ID, @I_PEOPLE_CODE_ID= PEOPLE_CODE_ID, @I_ACADEMIC_YEAR= ACADEMIC_YEAR, @I_ACADEMIC_TERM= ACADEMIC_TERM, @I_ACADEMIC_SESSION= ACADEMIC_SESSION, @I_EVENT_ID= EVENT_ID, @I_EVENT_SUB_TYPE= EVENT_SUB_TYPE, @I_SECTION= SECTION, @I_ATTENDANCE_DATE= ATTENDANCE_DATE, @I_ATTENDANCE_STATUS= ATTENDANCE_STATUS, @I_CREATE_DATE= CREATE_DATE, @I_CREATE_TIME= CREATE_TIME, @I_CREATE_OPID= CREATE_OPID, @I_CREATE_TERMINAL= CREATE_TERMINAL, @I_REVISION_DATE= REVISION_DATE, @I_REVISION_TIME= REVISION_TIME, @I_REVISION_OPID= REVISION_OPID, @I_REVISION_TERMINAL= REVISION_TERMINAL, @I_ABT_JOIN= ABT_JOIN, @I_COMMENTS= COMMENTS FROM inserted WHERE TranAttendanceId= @TranAttendanceId If (DB_NAME() = 'CAMPUS6' and (@I_ATTENDANCE_STATUS in ('ABSENT','TARDY'))) -- move into Select @TranAttendanceId lines someday? Begin EXEC institute.dbo.sp_absence@I_PEOPLE_CODE_ID,@I_ACADEMIC_YEAR,@I_ACADEMIC_TERM, @I_ACADEMIC_SESSION,@I_EVENT_ID,@I_EVENT_SUB_TYPE,@I_SECTION, @I_ATTENDANCE_DATE, @I_ATTENDANCE_STATUS, @TranAttendanceId ,@I_COMMENTS -- Ok, what real people would more likely do goes here from next slide End Select @TranAttendanceId = (Select Min( [TranAttendanceId] ) From inserted Where [TranAttendanceId] > @TranAttendanceId) --Get Next Id END

17. institute.dbo.sp_absence • Our sp_absence stored procedure actually checks multiple data sources (two off campus reports, and infirmary reports) to see if it can excuse the user, defer the decision (can’t make a decision before the end of class, if in the infirmary), or enter a “Stick” (our homegrown violation system). • Deferred decisions are checked again hourly • If not excused, they receive a Stick • Then an insert trigger on our Stick table checks for the same student 2 and more times today, and sends email • Depends a lot on Faculty prompt Attendance reporting

18. Absent trigger, generalized for non-NMMI • -- replace the “EXEC institute.dbo.sp_absence …” line in ti_tranattendance_nmmi with something like below, I did test this code • -- email variables • declare @esubject varchar(255), @emailto varchar(255), @ebody varchar(4000) • select -- define email variables only if sum for the day >1 • @esubject = PEO.LAST_NAME + ', ' + PEO.first_name + ' ' + right(PEO.people_code_id,9) + ' ABSENT '+ cast(COUNT(*) as varchar) + ' times today' • , @ebody = '<a href="https://www.nmmi.edu/.../crr.asp?id=' + PEO.People_Code_ID + '#absent">CRR Link</a>' • , @emailto = 'hsprincipal@nmmi.edu;foo@nmmi.edu;bar@nmmi.edu;bryan@nmmi.edu;' + case when EMPA.Email_Address IS not null then replace(empA.Email_Address + '@nmmi.edu',' ','') + ';' else '' end • from TRANATTENDANCE TA • join TRANATTENDANCE TA2 on TA.TranAttendanceId = @TranAttendanceId and TA2.People_Code_ID = TA.People_Code_ID • and TA2.ATTENDANCE_DATE = TA.ATTENDANCE_DATE -- = limits per date report, not limited in WHERE to allow testing with any date • join people PEO on PEO.PEOPLE_CODE_ID = TA.People_Code_ID • JOIN Academic ACA • on ACA.PEOPLE_CODE_ID = TA.PEOPLE_CODE_ID and ACA.ACADEMIC_YEAR = TA.ACADEMIC_YEAR and ACA.ACADEMIC_TERM = TA.ACADEMIC_TERM and ACA.ACADEMIC_SESSION = TA.ACADEMIC_SESSION • left join institute.dbo.Employees EMPA -- view of active employees • on EMPA.PEOPLE_ID = RIGHT(ACA.ADVISOR,9) -- or on 'P'+EMPA.PEOPLE_ID = ACA.ADVISOR -- which is better? • where TA2.ATTENDANCE_STATUS in ('ABSENT') and TA.ATTENDANCE_STATUS in ('ABSENT') -- add Tardy if you want • and CONVERT (date, GETDATE())= TA.ATTENDANCE_DATE -- limit to today, comment out for testing • group by PEO.People_Code_ID,PEO.LAST_NAME,PEO.first_name,EMPA.Email_Address having COUNT(*) > 1 • if (@esubject is not null) -- if email variables are not null, then send something off • begin • EXEC msdb.dbo.sp_send_dbmail@profile_name = 'DBmailprofile',@body_format = 'HTML‘, @from_address = 'bryan@nmmi.edu' • ,@subject = @esubject, @body = @ebody, @importance = 'High',@recipients = @emailto -- 'bryan@nmmi.edu' – switch around for testing • end

19. Email result

20. Summary, Questions • Please nit-pick my code, bryan@nmmi.edu • Should ANSI_NULLS ON and QUOTED_IDENTIFIER ON ? • Ok, Better, Best practices • Dependencies across databases • “Base View” starting points for departments • Download http://wordpress.nmmi.edu/computertraining • I’m staying on campus & love to talk geek • Other valid geek topics include Raspberry Pi, and • C# • I am also finishing a campus migration from Exchange 2007 to Office 365, if you want to talk about that My Info