My First Trigger

time to read 7 min | 1336 words

So, today I wrote my first trigger. The issue was of data replication on a hierarchial structure. When the parent is updated, all the children should be as well. If a child is update, the link to the parent is severed. The number of children is unlimited, so I can't use NHibernate to do the update, as it would be horribly inefficent to do batch update with it.

The parent and child in this case is one of ~15 classes (one inheritance family) that has different meaning for the same fields in the database. They are mapped using Table Per Inheritance in NHiberante (using ActiveRecord). At first I despaired from doing this properly, I thought about having to do it 15 times, with minor modifications... Even seperating the common functionality, I would still have a lot to write. Then I remembered that I'm using ActiveRecord, and that it can gives me the information about a class, so I quickly wrote this code:

/// <summary>
/// Copies all the values of the current parent rule to all its children
/// iterate over the model for the rule and gets all the properties and fields.
/// </summary>
protected virtual void UpdateChildren(IDbConnection connection)
 using (IDbCommand command = connection.CreateCommand())
  command.CommandText = Resources.UpdateAllChildRules;
protected virtual void CrearteParameters(IDbCommand command)
 ActiveRecordModel model = ActiveRecordBase.GetModel(this.GetType());
 foreach (PropertyModel propModel in model.Properties)
  CreateParameter(command, propModel.PropertyAtt.Column,
 foreach (FieldModel fieldModel in model.Fields)
  CreateParameter(command, fieldModel.FieldAtt.Column,
protected void CreateParameter(IDbCommand command, string name, 
 object value, Type type)
 IDataParameter param = command.CreateParameter();
 if (type.IsDefined(typeof(ActiveRecordAttribute),false))
  ActiveRecordModel model = ActiveRecordBase.GetModel(type);
  PrimaryKeyModel pkModel = (PrimaryKeyModel) model.Ids[0];
  type = pkModel.Property.PropertyType;
  value = pkModel.Property.GetValue(value, null);
 param.ParameterName = "@" + name;
 param.Value = value;
 param.DbType = GetDbType(type);

As I wrote this code, I kept muttering things about Evil Code and how the one to follow after wouldn't be able to understand anything. This is using some deeply internal knowledge of Active Record, and it's not very nice for someone who doesn't know how Active Record works. I just couldn't see a better way. And considerring my options, I thought that this was the least Evil among them.

Then a friend offered me to use a simple thing. Triggers. The database I'm using for the project in SQL Server 2000, so that is not a problem, but while I leapt at the idea (much clearner than the above code), but there was an implementation hurdle to go through, I never wrote a trigger before, certainly not for SQL Server. The only one around I could ask used to write triggers for Oracle, which wasn't much help.

So I scoured the books online, and discovered what I already knew, that SQL is a very different language than my safe OO world. I think that I got it write, and small tests shows that it's working properly, but I'm still not sure about it.

CREATE TRIGGER TR_Update_RuleChildren_On_Rule_Update     
ON [Rules]
DECLARE @UpdatedRuleId int, @UpdatedSeverity tinyint,
@UpdatedTemplateRuleId int
SELECT @UpdatedRuleId = [RuleId], @UpdatedSeverity = [Severity],
@UpdatedTemplateRuleId = [TemplateRuleId] FROM [Inserted]
IF (@UpdatedTemplateRuleId IS NULL) -- is a parent rule
UPDATE [Rules] SET [Severity] = @UpdatedSeverity
WHERE [TemplateRuleId] = @UpdatedRuleId
UPDATE [RULES] SET [TemplateRuleId] = NULL
WHERE [Rules].[RuleId] = @UpdatedRuleId

As I said, this is the very first I've written, so I'm absolutely not certain if I did it right. Is this the way to do? It make me cringe to see it, since it violate so many things I know are right. But I guess that they don't apply to a database.