Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 28th, 2007, 07:31 AM
Authorized User
 
Join Date: Feb 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Trigger for new recs

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




 
Old March 28th, 2007, 07:54 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old March 28th, 2007, 03:58 PM
Authorized User
 
Join Date: Dec 2006
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 29th, 2007, 01:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 30th, 2007, 05:02 AM
Authorized User
 
Join Date: Feb 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Kind regards,

John






Similar Threads
Thread Thread Starter Forum Replies Last Post
How-To: Form to insert recs in a One-to-Many convergent7 ASP.NET 2.0 Basics 5 July 15th, 2006 10:58 PM
Want to get number of recs without opening rs amjad_mahmood Pro VB Databases 2 August 10th, 2005 12:44 AM
How to get # of recs without opening recordset amjad_mahmood Excel VBA 0 August 9th, 2005 05:49 AM
Enumerate Master - Detail Query Recs JpJoe Access 5 May 17th, 2005 09:05 AM
Trigger realgone_ SQL Language 4 January 27th, 2005 11:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.