trigger vs default

22 Jun 2006 12:40 PM

Tom W

I downloaded some example code from MS and don't understand why this
trigger is being used. Wouldn't a column default accomplish the same
thing? Is there some reason a trigger is 'better' than a default? Is
the trigger meant to ensure that someone doesn't insert a different
value for [LastUpdate]?

CREATE TABLE [dbo].[Products] (
    [ProductID] [int] IDENTITY (1, 1) NOT NULL        CONSTRAINT
PKProductID PRIMARY KEY ,
    [ProductName] [nvarchar] (50) NOT NULL ,
    [CategoryID] [int] ,
    [UnitPrice] [money] NOT NULL ,
    [LastUpdate] [datetime]
) ON [PRIMARY]

CREATE TRIGGER trInsert ON dbo.Products
FOR INSERT
AS

Update
Products
Set
LastUpdate = GetDate()
From
Products p Inner Join Inserted i On p.ProductID = i.ProductID
Where
p.ProductID = i.ProductID

GO

tia
Tom

--

E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.

22 Jun 2006 12:46 PM

SQL Menace

If you supply a value then the default value won't be use hence you
could insert 'wrong' dates
The trigger assures that the date that is used will be GETDATE()

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Tom W wrote:
Show quote

> I downloaded some example code from MS and don't understand why this
> trigger is being used. Wouldn't a column default accomplish the same
> thing? Is there some reason a trigger is 'better' than a default? Is
> the trigger meant to ensure that someone doesn't insert a different
> value for [LastUpdate]?
>
> CREATE TABLE [dbo].[Products] (
>     [ProductID] [int] IDENTITY (1, 1) NOT NULL        CONSTRAINT
> PKProductID PRIMARY KEY ,
>     [ProductName] [nvarchar] (50) NOT NULL ,
>     [CategoryID] [int] ,
>     [UnitPrice] [money] NOT NULL ,
>     [LastUpdate] [datetime]
> ) ON [PRIMARY]
>
>
>
> CREATE TRIGGER trInsert ON dbo.Products
> FOR INSERT
> AS
>
> Update
>   Products
> Set
>   LastUpdate = GetDate()
> From
>   Products p Inner Join Inserted i On p.ProductID = i.ProductID
> Where
>   p.ProductID = i.ProductID
>
> GO
>
>
>
> tia
> Tom
>
> --
>
>
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.

22 Jun 2006 1:04 PM

Scott Morris

>I downloaded some example code from MS and don't understand why this
>trigger is being used. Wouldn't a column default accomplish the same
>thing? Is there some reason a trigger is 'better' than a default? Is the
>trigger meant to ensure that someone doesn't insert a different value for
>[LastUpdate]?

With the appropriate security, the default is preferable to the trigger.
However, that approach will not work with updates for this particular
purpose, requiring the use of an updated trigger.