Emanuele_Ciriachi
asked on
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?
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?
ASKER
Default will not work - I need the trigger to work every time the record is [b]updated[/b] as well.
ASKER
Damn, and I thought BBCode was on all the time...
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.
so your insert statements works without any error and the trigger is executed as it is now.
ASKER
Ok. Care to show an example of this?
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.
ASKER
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.
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.
Have you tried an instead of trigger?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
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).
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.
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.
>> 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.
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.
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.
ASKER
thank you.
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')