Trigger for new recs

John2112 · March 28th, 2007, 07:31 AM

Hello,

I have the following question.
I have a table with a date field. What I want is that for newly added records the date is filled in the field wbaprda. To avoid that all records in the table wba are updated with this date I use the trick of setting the default date on'"11-11-1911" and using this to limit the update to the just newly added records.
This is the source code of the trigger I am using

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [tr_wba_01]
   ON [dbo].[wba]
   AFTER INSERT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

UPDATE wba
SET wbaprda = convert(varchar(10), getdate(), 105)
WHERE wbaprda = '11-11-1911'
END

I expect that there is a better, more elegant, way to achieve the goal of adding the date to the field wbaprda of the new records. With this better way I mean without using the "11-11-1911" default trick.

Who can help? Many thanks in advance.

John

joefawcett · March 28th, 2007, 07:54 AM

Can't you just set the default value of the field to be GETDATE()? If you need finer grained control then you need to find the specific record(s) that has been updated in the trigger by using the built-in INSERTED and DELETED tables. Something like

Code:

INSERTED I LEFT OUTER JOIN DELETED D ON I.[<primary key field>] = D.[<primary key field>] WHERE D.[<primary key field>] IS NULL

.

--

Joe (Microsoft MVP - XML)

agossage · March 28th, 2007, 03:58 PM

A DEFAULT CONSTRAINT is useful when no value is supplied for the given column. By 'no value', I do not mean NULL. NULL is a value.

If the app or user supplies a value that is unacceptable and you want to completely disallow the value, a CHECK CONSTRAINT will do the trick. If you want the INSERT to succeed regardless of the value and back populate a acceptable date, a TRIGGER is the way to go.

In any case, TRIGGERs should only be allowed to modify rows that are within the scope of the transaction. For the TRIGGER supplied, there is a risk that other rows will be updated due to the lack of a INSERTED/DELETED virtual table reference. Since the rows in INSERTED and DELETED virtual tables contain only those rows that have been inserted/updated/deleted in the scope of the transaction, it is considered 'best practice' to utilize the INSERTED and DELETED virtual tables.

HTH

Adam Gossage
Lake Wylie, SC, USA

happygv · March 29th, 2007, 01:31 AM

Yes, you were not at all using the INSERTED/DELETED tables in your trigger. In your case INSERTED table is the one available during inserts. So your trigger could be something like this.

Code:

ALTER TRIGGER [tr_wba_01] 
   ON  [dbo].[wba]
   FOR INSERT
AS 
BEGIN
    UPDATE wba
        SET wba.wbaprda = convert(varchar(10), getdate(), 105)
        FROM wba, INSERTED
        WHERE wba.column_name = INSERTED.column_name
        -- Column_Name could be the primarykey or something unique that identifies the row.
END

The question here is ... Is wba.wbaprda not a DATETIME type?

Why don't you set DEFAULT value of that column as Getdate() than writing a trigger?

cheers.

_________________________
- Vijay G
Strive for Perfection

John2112 · March 30th, 2007, 05:02 AM

To all you guys (and especially happygv) thanks for the replies.
The default idea seems to be the best.

Kind regards,

John