OK, I'm still rather new to LLBLGen, and I'm trying to figure out the best way to accomplish in this paradigm what I would have done in the past with triggers.

My particular problem at hand is firing off email notifications when a particular entity property changes. So, for my "AssignmentEntity", whenever "myAssignmentEntity.SomeProperty" changes, I want to hook into the right event to send email notification.

I see all the "OnSave", "OnSaveComplete" and other such events are available to me, but how do I know which event to use, and how do I get access to both the old and new values to see if "SomeProperty" has changed?

Can someone help prevent me from going back to triggers?

Thanks.

Triggers are not evil, just make sure they call stored procedures, which can be better encasulated pieces of logic. Also, one use of triggers that can't be invalidated by app code is the when multiple apps hit an underlying DB schema. Many dev tools tend to be more session oriented, triggers tend to be used to implement more global handlng of database changes.

Doug_Hettinger wrote:

Triggers are not evil, just make sure they call stored procedures, which can be better encasulated pieces of logic.

True, but they are specific to a particular database platform. While all of my apps up to this point have been in SQL Server, I like the portability that comes with writing my code in LLBLGen.

Doug_Hettinger wrote:

Also, one use of triggers that can't be invalidated by app code is the when multiple apps hit an underlying DB schema. Many dev tools tend to be more session oriented, triggers tend to be used to implement more global handlng of database changes.

True again. But, I'm still interested in whether or not there are alternatives to triggers in LLBLGen. Especially because I want a "trigger" to fire in my business layer essentially allowing me to utilize .NET classes instead of SQL to accomplish what I need to do.

Do you (or anyone else out there) know of any such alternatives?

Of course I know an alternate approach that allows you to do what you need to do :-)

Create an extended stored proceedure and, if you like, wrap it in a traditional TSQL stored proc. Parameterize it on notification ID's (table and field maybe). In your DB triggers, simply call the extended stored procedure, which calls your .Net class methods. This is a pretty proven technique.

Uh, wrote:

Do you (or anyone else out there) know of any such alternatives?

What are you using? Self servicing or Adapter?

wayne wrote:

Uh, wrote:

Do you (or anyone else out there) know of any such alternatives?

What are you using? Self servicing or Adapter?

I'm using Self-Servicing.

I don't think there is any real replacement for a trigger as a database trigger fires when conditions are met never mind where the action came from. The solution i can give you can only work while the calls come from your applications DAL. If any modification are made to your DB by another app that does not use your DAL then the trigger wont fire.

My solution. (See my very rough diagram) You could edit your template and implement the following abstract factory to return a TriggerObj once the insert, update delete methods of the entity is called. Use the EntityType enumerator in ConstantsEnums.cs to identify each sub Entity trigger class.

If an update trigger is needed call the CreateUpdateTrigger of the TriggerFactory else call the CreateinsertTrigger.

This trigger object's ExecuteTrigger method should then be called which will then in turn call a protected method called InternalExecute. The InternalExecute method should be overridable so that each triggerobj can implement his own logic (Your custom code)

Note that the InsertTrigger and UpdateTrigger object classes might not be needed. You could infact derive directly from TriggerObj but they might be needed later. I only investigated insert and update but i am sure delete should also be possible.

Well that is how it could be done. I hope this helps a bit.')

Wayne is right, there is really no replacement for triggers. Especially when dealing with a schema hit by many apps - which is obviosuly very common in enterprise apps.

Wow, that was a quick solution, wayne! Thanks for your input.

In reviewing my options, I've decided to go with the following approach below, but first, a bit more detail on the problem so the approach makes sense in the context of a real problem.

I have an "Assignment" table, which has a column "bmkTableState". I had named it "bmkEntityState" but it turns out that LLBLGen already has an enum by that name. (Thanks for hogging all the good terms, Frans .) The "bmkTableState" column will change when the user (person who the Assignment is assigned to) says the state of the Assignment has changed (e.g. from Assigned to Completed).

So here's how I'll handle sending notifications:

1. Put a trigger on Assignment table

if updated (bmkTableState)
begin
   insert TransitionQueue 
   (
      bmkOldTableState, 
      bmkNewTableState, 
      TableName, 
      bmkTableRecord
   )
   select
      d.bmkTableState,
       i.bmkTableState,
       'Assignment',
       bmkAssignment
    from inserted i
       inner join deleted d
          on i.bmkAssignment = d.bmkAssignment
end

1. Create a new module in my already existing Windows Service

2. Poll the database at specified interval to look for new TransitionQueue's (using LLBLGen business objects, of course)

3. Create a TransitionHandlerFactory that based on the TransitionQueue.TableName creates an ITransitionHandler of the correct type (concrete class for every table that handles state transitions)
4. AssignmentTransitionHandler would process the record that was saved, and send a notification to the correct person (whoever the Assignment is now assigned to)

That's kind of a rough sketch too, but the basic idea is there.

I am interested in the forthcoming "Windows Workflow Foundation" and State Machines in general as a means to solve this problem, but I need something now.

Thanks again for your help wayne and Doug.