Transcript Some cool Triggers - New Mexico Military Institute

SOME COOL NMMI
SQL TRIGGERS
by Bryan Yates, [email protected]
New Mexico Military Institute in Roswell, NM
http://WordPress.nmmi.edu/ComputerTraining
My Info
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.
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_date datetime NULL
, create_time datetime NULL, create_user varchar(12) NULL, revision_date datetime NULL
, revision_time datetime NULL, revision_user varchar(12) NULL
•
•
•
•
•
•
•
•
go
CREATE TRIGGER ti_audit_<TABLETOALTER,,> ON <TABLETOALTER,,>
FOR INSERT
AS
/* Audit Fields */
declare @I_Id varchar(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_Id varchar(20)
select @I_Id = <UNIQCOL,,id> from Inserted
exec sp_Audit_Revision '<TABLETOALTER,,>','<UNIQCOL,,id>',@I_Id
go
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
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), @subjectText varchar(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 = '[email protected]',
@body = @results,
@subject = @subjectText
GO
New database table, the email
Database Mail
View existing Email profiles
• exec msdb.dbo.sysmail_help_account_sp
columns
account_id
name
description
email_address
display_name
replyto_address
servertype
servername
port
username
use_default_credentials
enable_ssl
rows
2
Programmers
ISCT Programmers
[email protected]
Programmers
[email protected]
SMTP
email.nmmi.edu
25
NULL
0
0
3
SQL Server Mailer
[email protected]
DBMailAccount
[email protected]
SMTP
email.nmmi.edu
25
NULL
0
0
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
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
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
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 .
StudentFinancial trigger
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
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
StudentFinancial, Supporting data
select * from Institute.dbo.BO_STMT_Financial_Advisor -- drives triggers, and pre/billing groupings
PaymentPlanCode
1FA
2FA
3FA
4FA
5FA
6FA
AGA
FIRST_LETTER
ABC
HIJKL
DEFG
MNO
PQRS
TUVWXYZ
NULL
AdvisorName
Mrs. Angelia Horton
Ms. Shelly Wheeler
Ms. Rebecca Quintero
Mrs. Sarah Villarreal
Mrs. Linda Stansell
CPT Ma Eva Heacox
CPT Amanda Garcia
AdvisorPhone
(575) 624-8083
(575) 624-8082
(575) 624-8406
(575) 624-8084
(575) 624-8077
(575) 624-8089
(575) 624-8086
username
angelia
shelly
quinter
sarah
stansell
ma_eva
garciaa
select * from campus6.dbo.CODE_PAYMENTPLAN -- shows in PowerCAMPUS
CODE_VALUE_KEY
1FA
2FA
3FA
4FA
5FA
6FA
AGA
CODE_VALUE
1FA
2FA
3FA
4FA
5FA
6FA
AGA
SHORT_DESC
1FA
2FA
3FA
4FA
5FA
6FA
AGA
MEDIUM_DESC
Mrs. Angelia Horton
Ms. Shelly Wheeler
Ms. Rebecca Quintero
Mrs Sarah Villarreal
Mrs. Linda Stansell
CPT Ma Eva Heacox
CPT Amanda Garcia
LONG_DESC
Mrs. Angelia Horton at (575) 624-8083
Ms. Shelly Wheeler at (575) 624-8082
Ms. Rebecca Quintero at (575) 624-8406
Mrs. Sarah Villarreal at (575) 624-8084
Mrs. Linda Stansell at (575) 624-8077
CPT Ma Eva Heacox at (575) 624-8089
CPT Amanda Garcia at (575) 624-8086
STATUS
A
A
A
A
A
A
A
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
Absent 2 or more times in one day?
CREATE TRIGGER [dbo].[ti_tranattendance_nmmi] ON [dbo].[TRANATTENDANCE] FOR INSERT
AS
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
@I_PEOPLE_CODE nvarchar(1)
@I_PEOPLE_ID nvarchar(9)
@I_PEOPLE_CODE_ID nvarchar(10)
@I_ACADEMIC_YEAR nvarchar(4)
@I_ACADEMIC_TERM nvarchar(10)
@I_ACADEMIC_SESSION nvarchar(10)
@I_EVENT_ID nvarchar(15)
@I_EVENT_SUB_TYPE nvarchar(4)
@I_SECTION nvarchar(4)
@I_ATTENDANCE_DATE datetime
@I_ATTENDANCE_STATUS nvarchar(10)
@I_CREATE_DATE datetime
@I_CREATE_TIME datetime
@I_CREATE_OPID nvarchar(8)
@I_CREATE_TERMINAL nvarchar(4)
@I_REVISION_DATE datetime
@I_REVISION_TIME datetime
@I_REVISION_OPID nvarchar(8)
@I_REVISION_TERMINAL nvarchar(4)
@I_ABT_JOIN nvarchar(1)
@I_COMMENTS nvarchar(max)
@ROWS INT
@TranAttendanceId int
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
-- 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
@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
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
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 = '[email protected];[email protected];[email protected];[email protected];' + 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?
•
group by PEO.People_Code_ID,PEO.LAST_NAME,PEO.first_name,EMPA.Email_Address having COUNT(*) > 1
•
•
•
•
•
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
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 = '[email protected]'
,@subject = @esubject, @body = @ebody, @importance = 'High',@recipients = @emailto -- '[email protected]' – switch around for testing
end
Email result
Summary, Questions
• Please nit-pick my code, [email protected]
My Info
• 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