Solved

Trigger to update BEFORE column constraints are checked

Posted on 2010-09-16
15
346 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 256 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
SQL Server Designer 19 40
migrate a SQL 2008 to 2016, 2 29
Syntax for query to update table 2 8
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 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