CODEWITHSUNDEEP
sql-servert-sqltriggers
Marshall
Marshall

Reputation: 648

Using triggers to alter inserted/updated data to enforce a business rule

I've just created a trigger (AFTER INSERT, UPDATE) that is designed to strip the time from a date field. I just figured that a trigger would be easier to implement than having to fix it in the application that inserts/updates the table. The latter would involve re-compiles, and kicking folks out of the app for updates.

But I'm not sure if I'm being lazy or clever, to be honest.

CREATE TRIGGER [dbo].[StripCastDateTime] ON [dbo].[PileInventory]
    AFTER INSERT, UPDATE
AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
       SET NOCOUNT ON;

       UPDATE PileInventory
          SET CastDate = 
                     cast(CONVERT(varchar(11), Inserted.CastDate, 101) AS datetime)
          FROM INSERTED 
          INNER JOIN [dbo].[PileInventory] AS PI
          ON     [PI].[JobNumber] = INSERTED.JobNumber
             AND [PI].[MarkNumber] = INSERTED.MarkNumber
          WHERE NOT Inserted.CastDate IS NULL 


    END

GO

It works perfectly, but is this a proper use of triggers?

Thanks.

Marshall

Upvotes: 0

Views: 206

Answers (2)

jean
jean

Reputation: 4350

I can put a business rule in the business layer. Lazy ones needs to work twice is a old said here and if you can kick people off the application in a maintenance window do it. If you do a a lot of inserts that trigger can slow down your app, also triggers are quick to grow complex and add some maintenance pain.

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

If your updated tables are big then this would hurt your DB. I would use a trigger "INSTEAD OF INSERT" to change the data and after that insert it. There is no point in doing it after it is in the DB.

Other then that .. it's a valid usage of triggers.

Read more here: http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

Upvotes: 1

Related Questions