Solved

Trigger to update BEFORE column constraints are checked

Posted on 2010-09-16
15
326 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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:ScottPletcher
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now