SQL query to find existing triggers in your DB

USE myAwesomeDatabase
SELECT  table_name = OBJECT_NAME(parent_object_id) ,
        trigger_name = name ,
        trigger_owner = USER_NAME(schema_id) ,
        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
          WHEN 1 THEN 'Disabled'
          ELSE 'Enabled'
        END AS status
FROM    sys.objects
WHERE   type = 'TR'
ORDER BY OBJECT_NAME(parent_object_id)
This works great – very handy to have when you need to see what triggers are on what tables in your database!
PS – I used the sample code tag in the HTML view to ‘force’ the single quotes to behave correctly in the code snippet above. Thank you to HTML Goodies for a great article on this!
PSS – I should also add that I great information with these 2 articles regarding triggers – UBER-helpful!!

Co-op 2 Highlights 14

It’s funny, but I have been about a week behind all semester – I’m not sure how that happened, but we’ve come down to the last week of classes and I will be posting #14 here today and #15 sometime in the couple of days.  I met last night with the capstone instructor and we tweaked just a couple of things on VARI (Vineyard Attendance Reporting Index).  One of those was to show just one date near the delete button instead of looping through the model to see how many events could have occurred on that day (it could be 2 at Middletown or 10 at Tri-County).  I didn’t want to have 10 dates of the same date showing next to the delete button.  The other thing we tweaked was styling my back/cancel button to make it look like a button, while really being a link.  There are several opinions and ways about how best to do this.  With MVC, you can’t have more than one submit button on a form, so one way to get around that it to have 2 or 3 forms on a web page, each with their own submit button.  With the situation of a cancel button, it doesn’t really get ‘submitted’ anywhere, it would just redirect the user back to the home/starting page, so it’s more like a link in that aspect.  This post, from StackOverflow, gave some options on how to do that.

I’ve started adding in the historical data from the original Excel spreadsheet, so that everything is converted over to the SQL database.  This is easy in a way, but time-consuming.  For things like the Saturday night church service or the Wednesday or Friday services, those are all listed with the Sunday date for that week, so the dates need to be adjusted for each of those.  I’m unsure yet about how much historical data to include, but for now, I will import the last year to be able to provide some decent reporting comparisons.

Reporting – the final hill!  One of our web/IT people at work suggested using the Google Charts API and I’ve begun implementing that.  It almost seems to easy!  I need to read up on how to connect my datasource (the database) to the Google javascript, so that the chart is pulling data from the database (and not the pizza array that Google gives as an example).  There has never been really great dynamic ways of reporting on the attendance data, so I think it could prove pretty valuable.  It’s one thing to see a plus or minus number on an Excel spreadsheet and say ‘oh, we are down for the month,’ but it is a whole new world to see that represented in color on a pie chart or bar chart.  This introduces the infographics topic again – I think I wrote about that earlier this semester or last semester.  Infographics are graphical representations of data.  The use of color, trendlines, sizes, and shapes really help to convey the meaning of the data they represent.

I’m planning to present this project to our IT team and a couple of other interested parties at work next week.  Then, I can get feedback and together, we can determine if it is a useful enough product to present to the leadership team and possibly launch for real-world use.

Co-op 2 Highlights 13

So, I’m catching up now on my posts here.  I had fallen a bit behind on posting, but not for lack of ideas or things to write about, rather a lack of time to put fingers to keyboard (like putting pen to paper).  🙂

My capstone project has been pretty much all-consuming lately and I am starting to see the end of the tunnel – it is a real, live attendance tracking tool.  You can enter attendance data for a specific date for a specific location and church time and save it.  And, it really does save into a database!  One of the things that I’ve added is a jquery datepicker, which has been a nice addition.  My capstone instructor has been very helpful with this project and maybe even a little stumped at times.  Between the two of us (him:  logic and syntax, me:  double-checking commas, column names, datatypes), we created a stored procedure to create a temp db – this will check to see if any attendance record already exists for that particular date.  If so, it allows you to update that attendance.  If not, it creates a record.  That was a huge breakthrough about 3-4 weeks ago now.  Connecting the datepicker was helpful and allows the end user to just click on a date, instead of typing in the date (and already knowing that Sunday was April 6 instead of April 5).

One thing I’ve realized though about our historical attendance data, which I am now importing in to the database, is that all records are based on a Sunday date, regardless of whether they happened on Wednesday, Friday, or Saturday.  Over the years, that has been the structure and it has worked, but in a pretty rudimentary way.  The Wednesday and Friday attendance numbers are in the same column, so you can’t really compare or analyze those numbers accurately because you don’t know if something was a Good Friday service or a Wednesday night church service.  I have to think through this a little further, but I am leaning to the side of correcting all of those items as they are imported into the new database.  It makes it more accurate, and databases are all about accurate.

Another thing I have wrestled with in the past 2 weeks is a datetime format for my data – I found this website with a great listing of examples for any type of format you may want.  I am still fine-tuning my date and time listings.  The times show up in 24-hr format, instead of AM/PM and I think the AM/PM would be a more user-friendly way to display the church times.

Reporting is one of the final big hills to tackle with this and I’m not sure how pretty it’s going to end up.  The project is based on MVC overall, but MVC and WebForms can intermingle within the same project.  I’ve found some good information here and here about this.  Actually making that happen doesn’t seem to be quite as easy.  I do have a basic gridview on a Reports page that allows sorting and paging of data, so you could sort by date, time, location, notes, or even attendance count.  I would love to find a great way to create a visual or infographic to display this data.  I’m still working on this and hopefully will be able to post about my success in my next post (#14).

One last piece is implementing a log-in system on this project, so not just anyone could enter data.  That will take a bit more research.


Co-op 2 Highlights 12

Well, it seems that I have been so busy in the last 2 weeks, I have fallen a bit behind.  That seems to be a theme for this semester – I’m keeping up with things in other circles, but have dropped my activity level here.  I have encountered all kinds of new information in the last couple of weeks, so I will be sharing that in my next post.

For this post, I will concentrate on the website updates I have been making.  Our sister organization is participating in a huge event at the end of May called the Hunger Walk.  Each year, this project includes updating the website to host several dedicated pages of information with links to registration pages, supporting information, social media connections, and an online payment/donation form connected to our payment gateway.

Making sure all of those things are updated and working correctly has been a focus.  During this season each year, these pages are made active or visible.  I have also activated our registration on the Freestore Foodbank’s website and created a short link to use in print pieces to help steer people to register.  This included a meeting with a couple of representatives from the Freestore Foodbank where I was able to give feedback on their Convio registration system.  In addition to updating several web pages, I am also working on writing the content and scheduling posts for both Twitter and Facebook.  Although that is not really relevant to true coding or programming, the social media aspect plays a big part in an organization’s web presence, so getting it right is important.  We also host a web page that can collect online donations, run them through our payment gateway, and updates a database with their donation.  This last part includes working with a database to make sure that any Hunger Walk-specific donations that come in are tagged with the correct GL code and the correct campaign code for this year (HW2014).  Doing this now, before donations start coming in, is important and relieves the headache of manually updating records with the correct campaign code.

This particular project requires a lot of project management.  There are a lot of moving parts, internal deadlines, external communications, and print pieces to be designed and printed.  Again, although this is not really related to coding and programming, the project management piece is important.  As a web developer working on a project, I will need to know what needs to be done, what the scope of the project is, what has been completed already, and what deadlines exist.  So, familiarity with project management is a plus in just about any realm nowadays.

I’ve also been involved with uploading Powerpoint files and Google forms (surveys) to this website for another program and have again found that great customer service makes for happy people.  I’ll be posting more soon with links to some of the information I’ve learned from working on my attendance reporting project.