[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Trigger to update BEFORE column constraints are checked

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
Emanuele_Ciriachi
Asked:
Emanuele_Ciriachi
  • 7
  • 3
  • 2
  • +2
1 Solution
 
appariCommented:
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
 
Emanuele_CiriachiAuthor Commented:
Default will not work - I need the trigger to work every time the record is [b]updated[/b] as well.
0
 
Emanuele_CiriachiAuthor Commented:
Damn, and I thought BBCode was on all the time...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
appariCommented:
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
 
Emanuele_CiriachiAuthor Commented:
Ok. Care to show an example of this?
0
 
BrandonGalderisiCommented:
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
 
Emanuele_CiriachiAuthor Commented:
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
 
BrandonGalderisiCommented:
Have you tried an instead of trigger?
0
 
Emanuele_CiriachiAuthor Commented:
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
 
jvejskrabCommented:


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
 
Emanuele_CiriachiAuthor Commented:
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
 
jvejskrabCommented:
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
 
BrandonGalderisiCommented:
>> 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
 
Scott PletcherSenior DBACommented:
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
 
Emanuele_CiriachiAuthor Commented:
thank you.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now