?
Solved

Trigger to update BEFORE column constraints are checked

Posted on 2010-09-16
15
Medium Priority
?
361 Views
Last Modified: 2012-05-10
I have the following problem: one of my table fields, record_dttm, is updated via trigger every time a new record is created/updated, storing the current getdate() in it. Essentially, the field will never be empty: every time a record is modified, the trigger will automatically generate a new value.
It would then make sense to specify the field as NOT NULL, but this is currently impossible for me: if I try to insert a record that does not specify it, the Database complains that record_dttm cannot be null, BEFORE my trigger can actually fill it.

So, in short: how can I write a trigger so that it updates the value of record_dttm *before* SqlServer checks whether the field is null or not?
0
Comment
Question by:Emanuele_Ciriachi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 39

Expert Comment

by:appari
ID: 33697899
Why dont you define default constraint along with not null on that column?
the following works fine without any errors. if the col3 value is not specified in the insert value list it will update the default value. if they do include col3 in the insert list it takes the value from the insert statement.

CREATE TABLE [dbo].[testDef](
      [Col1] [varchar](10) NULL,
      [Col2] [varchar](50) NULL,
      [Col3] [datetime] NOT NULL CONSTRAINT [DF_testDef_Col3]  DEFAULT (getdate())
)

insert into [testDef](col1,Col2)
values('123','1234')
0
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33697919
Default will not work - I need the trigger to work every time the record is [b]updated[/b] as well.
0
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33697921
Damn, and I thought BBCode was on all the time...
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 39

Expert Comment

by:appari
ID: 33697956
you can have trigger along with the default.
so your insert statements works without any error and the trigger is executed as it is now.
0
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33698028
Ok. Care to show an example of this?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33698093
A default won't work.  A default constraint is only applied when the field is NOT specified.  If you SPECIFY NULL, the default will not make it NOT-NULL.
0
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33698118
it's ok, I already managed to change the field to NOT NULL - I would like it to stay this way.

What I want on top of that, is a trigger that fills up the field before SQLServer will check for null.

Example: if I have a table with two fields, the 1st one is a normal varchar and the 2nd is my automatic record_dttm, if I insert/update just the first one I want the second to change as well. I would like the 2nd one to be NOT NULL, but before the trigger can work, SQLServer complains that I did not specify the value for a field that cannot be null.

I hope I made myself clear.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33698153
Have you tried an instead of trigger?
0
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33699850
Hi Brandon, was that a typo?
If it was not, then I apologize but I am a SQL Server beginner and could not understand what you meant with your last comment.
0
 
LVL 3

Accepted Solution

by:
jvejskrab earned 1024 total points
ID: 33700962


I don't know, why you just cant pass some nonsence value in INSERT/UPDATE and then do the logic in the trigger, but here is the code for INSTEAD OF TRIGGER



CREATE TABLE [dbo].[test](
      [id] int NOT NULL,
      [s] varchar(50) NULL,
      [dateModified] datetime NOT NULL
)
GO

CREATE TRIGGER [dbo].[tTestInsert]
   ON  [dbo].[test]
   INSTEAD OF INSERT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      INSERT INTO test (id, s, dateModified) SELECT id, s, getdate() FROM INSERTED

END

GO


CREATE TRIGGER [dbo].[tTestUpdate]
   ON  [dbo].[test]
   INSTEAD OF UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      UPDATE test
            SET s = i.s,
                  dateModified = getdate()
            FROM test t
                   JOIN INSERTED i ON i.ID = t.id

END

GO

INSERT INTO test (id, s) VALUES (1, 'abc')
INSERT INTO test (id, s) VALUES (2, 'bcd')

UPDATE test
      SET s = 'xxx'
      WHERE id = 1


0
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33701149
So, this "Instead of Insert" trigger will cancel the Insert in progress and perform an operation of my choice in place of it?
Wouldn't that prevent the original INSERT statement that I was trying to insert?

As I said, the idea is that the INSERT statement only fills some field of the record - the trigger will then take care of managing the automatic ones (which I currently cannot because they are NOT NULL, and the original INSERT fails because of this).
0
 
LVL 3

Expert Comment

by:jvejskrab
ID: 33701386
Yes "INSTEAD OF TRIIGGER" prevents the original one.
Thats why there is the command:
INSERT INTO test (id, s, dateModified) SELECT id, s, getdate() FROM INSERTED
In INSERTED pseudo table are rows you have originally inserted

So you can do whatever you want with your data before INSERT and finally insert them in the underlaying table or in another table if you want.

But i'm still confused, why you dont use NOT NULL column with DEFAULT value GETDATE() for insert operations and the UPDATE TRIGGER for update operations.
Then call INSERT statement ommiting the datetime column.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33701488
>> the trigger will then take care of managing the automatic ones


Try the instead of, or REMOVE THE COLUMN FROM THE INSERT LIST.  A default constraint will work if the column is not specifically inserted as NULL.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33701585
If the trigger will always update the value anyway, let the column be NULLable.  Your triggers can guarantee that it will never actually be NULL, but your INSERT/UPDATE statements won't get an error.
0
 
LVL 1

Author Closing Comment

by:Emanuele_Ciriachi
ID: 33781013
thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question