I made a small sample for you
create for each table a trigger for Inserting, Updating and deleting. Perhaps use a History table for each table , this way all your changes are recorded as well
Create a table as follows
Create Table Notifications
(
Id bigint Identity(1,1) Not null,
TableName nvarchar(250),
ChangedOn DateTime,
Notified bit
)
each time your trigger happens write to the notifications table the following values:
Insert Into Notifications
(TableName, ChangedOn, Notified)
Values
('YourTableName', GetDate(), 0)
Create an application with an easy timer that will periodically check the table values as follows:
ie: C#
private List<Notification> _notificationsToShow;
Private void DoNotify()
{
var notifs = Notifications.Where(t=> !t.Notified).ToList();
if(notifs.Count > 0)
{
_notificationsToShow.AddRange(notifs);
foreach(var Notification in notifs)
{
//Save to database as user is notified
Notification.Notified = true;
Notification.Save();
}
}
DoSomethingAlertish();
}
private void DoSomethingAlertish()
{
if(_notificationToShow.Count == 0 || _notificationToShow == null)
return;
var s = new StringBuilder();
foreach(var v in _notificationToShow)
{
s.AppendLine(v.TableName);
}
alert(s.ToString());
}
//Create an event that will empty the list (user must do trigger this action)
private void UserNotifiedAction()
{
_notificationToShow = new List<Notification>();
DoSomethingAlertish();
}
The downsize of this method is the following:
for each table triggers must be created. This means 3 triggers per table
so when you have a small application with for example 15 tables this already means the creation of 45 triggers, now do think of a situation with 1000 tables, you get the point.
Also, be aware that the Notifications table will grow like a maniac. So perhaps you need to clean it now and then.
the good part is then that after creating all your triggers and storing the values in the table, you can use whatever technology available to create a notification. You want it in the tray bar now, perhaps in the future you still want a mail, everything is already running and collecting all your values, the only part where you then have to think about is what technology to call it with.
I hope i provided you with a solution that can help you in the future. Again, the solution you ask can be simpler but it depends how future-aimed you are.
