April 16, 2015 at 10:47 am
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
DateAdded DATETIME NULL,
DateModified DATETIME NULL
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED (ID ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
Kindest Regards,
Just say No to Facebook!April 16, 2015 at 1:47 pm
Use an AFTER trigger(s) for both INSERT and UPDATE.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 16, 2015 at 2:10 pm
Thanks for replying. We tried an AFTER TRIGGER and while this works to ensure that the DateCreated and DateModified are set accordingly and this has resulted in another issue.
Using the same example before we have another table named MYTABLE_AUDIT that is identical to the first with a few additional fields that are used for DML auditing. When ever an existing row in MYTABLE is updated or Deleted the row is copied to MYTABLE_AUDIT so that the _AUDIT table retains a history of changes. When we use an AFTER TRIGGER to unsure that DateCreate and DateModified are updated this results in the MYTABLE_AUDIT table containing 2 rows for every 1 row that is updated or deleted from MYTABLE.
We can't change the table structures because this DB is used by a software app we did not create so anything we do has to be passive and this is why we can change NULLS in DateCreated and DateModified to real dates via Trigger and not break anything with the software that uses this DB.
Below is the DCL for the _AUDIT table. Anyone have any ideas on how to accomplish the same goal but not get 2 rows in MYTABLE_AUDIT for every row in MYTABLE that is deleted or updated?
CREATE TABLE dbo.MYTABLE_AUDIT(
AID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
EventDate DATETIME NOT NULL,
EventType CHAR(1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
DateAdded DATETIME NULL,
DateModified DATETIME NULL
CONSTRAINT [PK_MYTABLE_AUDIT] PRIMARY KEY CLUSTERED (AID ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Kindest Regards,
Just say No to Facebook!April 16, 2015 at 2:10 pm
I handle this scenario in databases I create by setting a default value of GETDATE() for DateAdded
I use an update trigger to set the value of DateModified.
If you want these to be "hard" values (i.e. set by the system whether the user or an application supplies a value or not), then use both insert and update triggers and explicitly set the value. If you want them to be defaults in the absence of a value supplied by a user or application then set a default for the DateAdded column (no insert trigger is required) and then in the update trigger test to see if the user supplied a value and use that if provided or GETDATE() if not.
April 16, 2015 at 2:25 pm
There's only four options I can see:
1. include the date value in the statement.
2. have a for insert/update trigger(s) that will add the date
3. have an after insert/update trigger(s) that will add the date
4. create a default
1 & 4 seem to be out due to it being a outside party application/database.
2 & 3 will work.
2 will reduce the entries in the audit table since it happens before the table is affected.
3 will create two entries in the audit table since the change happens afterwards.
Only you can determine which is the best option. But I'd ask, what is the issue of having two entries? They aren't the same as one has null and one will have the date. It shows how the record was first entered and the change made...that's a good way to check the trigger is working properly.
If the audit table is getting too large, how long do you need to keep the audit data? Maybe have a job that once a month deletes audit data that is not needed any more.
-SQLBill
April 16, 2015 at 2:36 pm
CREATE TRIGGER MYTABLE_AUDIT__TRG_INSERT_UPDATE
ON dbo.MYTABLE_AUDIT
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE ma
SET ma.DateModified = GETDATE()
FROM MYTABLE_AUDIT ma
INNER JOIN inserted i ON
i.AID = ma.AID
WHERE
i.DateModified IS NULL;
GO --end trigger
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 16, 2015 at 6:03 pm
Why has no one mentioned using an INSTEAD OF INSERT TRIGGER?
It would seem to me that this would take care of the audit issue.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 17, 2015 at 2:53 am
dwain.c (4/16/2015)
Why has no one mentioned using an INSTEAD OF INSERT TRIGGER?It would seem to me that this would take care of the audit issue.
Here's a quick example:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
DateAdded DATETIME NULL,
DateModified DATETIME NULL
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED (ID ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER dbo.SetDateAdded
ON dbo.MYTABLE
INSTEAD OF INSERT, UPDATE
AS
SET NOCOUNT ON;
IF EXISTS
(
SELECT 1
FROM DELETED
)
-- Then it's an UPDATE
UPDATE a
SET FirstName = b.FirstName
,LastName = b.LastName
,DateAdded = COALESCE(b.DateAdded, c.DateAdded, GETDATE())
,DateModified = b.DateModified
FROM dbo.MyTable a
JOIN INSERTED b ON a.ID = b.ID
JOIN DELETED c ON a.ID = c.ID;
ELSE
INSERT INTO dbo.MyTable
(FirstName, LastName, DateAdded, DateModified)
SELECT a.FirstName, a.LastName, ISNULL(a.DateAdded, GETDATE()), a.DateModified
FROM INSERTED a;
GO
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
UPDATE dbo.MYTABLE
SET DateAdded = NULL
WHERE ID = 1;
SELECT *
FROM dbo.MYTABLE;
GO
DROP TABLE dbo.MYTABLE;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 17, 2015 at 6:48 am
The on-going maintenance is higher for an INSTEAD OF trigger, since every column addition or deletion, and some column changes, all requires changes the trigger, which is not the case with an AFTER trigger on only specific columns.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 19, 2015 at 5:30 pm
ScottPletcher (4/17/2015)
The on-going maintenance is higher for an INSTEAD OF trigger, since every column addition or deletion, and some column changes, all requires changes the trigger, which is not the case with an AFTER trigger on only specific columns.
Agreed on the extra maintenance for INSTEAD OF TRIGGERs. I was just wondering why no one mentioned it as an alternative.
That's why, for every INSTEAD OF TRIGGER I have, I also have a DATABASE TRIGGER that reminds me of the need to maintain that TRIGGER in the event of a change to the structure of the table the TRIGGER is defined on. Well, one db TRIGGER actually, that has an internal table of tables on watch.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 20, 2015 at 7:31 am
dwain.c (4/19/2015)
ScottPletcher (4/17/2015)
The on-going maintenance is higher for an INSTEAD OF trigger, since every column addition or deletion, and some column changes, all requires changes the trigger, which is not the case with an AFTER trigger on only specific columns.Agreed on the extra maintenance for INSTEAD OF TRIGGERs. I was just wondering why no one mentioned it as an alternative.
That's why, for every INSTEAD OF TRIGGER I have, I also have a DATABASE TRIGGER that reminds me of the need to maintain that TRIGGER in the event of a change to the structure of the table the TRIGGER is defined on. Well, one db TRIGGER actually, that has an internal table of tables on watch.
Agree on the need to track it later. I have code that regenerates the trigger (the required custom code has special delimiters in it so the code generator "understands" it). Even with that, I don't use INSTEAD OF triggers unless I really need, either for functionality or reduced overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 20, 2015 at 5:37 pm
ScottPletcher (4/20/2015)
dwain.c (4/19/2015)
ScottPletcher (4/17/2015)
The on-going maintenance is higher for an INSTEAD OF trigger, since every column addition or deletion, and some column changes, all requires changes the trigger, which is not the case with an AFTER trigger on only specific columns.Agreed on the extra maintenance for INSTEAD OF TRIGGERs. I was just wondering why no one mentioned it as an alternative.
That's why, for every INSTEAD OF TRIGGER I have, I also have a DATABASE TRIGGER that reminds me of the need to maintain that TRIGGER in the event of a change to the structure of the table the TRIGGER is defined on. Well, one db TRIGGER actually, that has an internal table of tables on watch.
Agree on the need to track it later. I have code that regenerates the trigger (the required custom code has special delimiters in it so the code generator "understands" it). Even with that, I don't use INSTEAD OF triggers unless I really need, either for functionality or reduced overhead.
I've heard of such code "regenerators" but I've never tried to write one myself. You should maybe write an article for SSC on the topic to share your knowledge and technique. I for one would be first in the queue to read it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 21, 2015 at 2:15 am
You could try this in the update audit trigger:
IF TRIGGER_NESTLEVEL(OBJECT_ID('[<Schema,sysname,dbo>].[<Trigger,sysname,TriggerName>]'), 'AFTER', 'DML') > 0 BEGIN
-- Triggered by insert trigger ...
RETURN;
END
... where the trigger-name is that of your insert trigger.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply