Trigger: Changing Value with no UniqueID on table 2

[ousoonerjoe]

I need a TRIGGER that can replace '1/1/1900' with NULL on INSERT or UPDATE. However, I have an issue with the fact that the table may or may not have a UNIQUE identifier. I threw together the following when the table does have a UniqueID, but not quite sure how to handle a table that does not have one.

CREATE TRIGGER [trg_DateTest_Nulls]
ON DateTest
FOR INSERT, UPDATE
AS

DECLARE @Date	DATETIME
SET @Date = (SELECT StartDate FROM Inserted)

IF @Date = '1/1/1900'
	UPDATE DateTest
	SET StartDate = NULL
	WHERE DateID = (SELECT DateID FROM Inserted)

SET @Date = (SELECT EndDate FROM Inserted)
IF @Date = '1/1/1900'
	UPDATE DateTest
	SET EndDate = NULL
	WHERE DateID = (SELECT DateID FROM Inserted)

Any assistance or direction you can offer would be highly appreciated.

thank you.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[SQLSister]

Add an Unique identifier to any table without one. NO table should ever be without a unique identifier or unique index. Ever.

Your trigger will not work properly in any event.You canot assume that only one record at atime will bei nserted in atrigger. YOu would end up updating all the records inserted if any one met your criteria. Very bad trigger.
try something more like

UPDATE DateTest
    SET StartDate = NULL
from DATETest d
join inserted i on d.dateid = i.dateid
    WHERE StartDate = '1/1/1900'

"NOTHING is more important in a database than integrity." ESquared

 

[ousoonerjoe]

Unfortunately it's a conversion from mainframe. We're getting rolled over to a relational based structure, but some things like TableID's were not added. We're working them in, but takes a little time at this point. I would have added them to the CREATE TABLE statements in the beginning, but then that was a year before I got here.

Elegant. That is much simpler than I had it. However, it still doesn't answer the question entirely.... Is it possible with out a UniqueID? My first thought is no, but my first thoughts have been wrong before.


--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[gmmastros]

It's possible, but probably not a good idea (from a performance perspective). In the code SQLSister showed you, she joined the real table to the inserted table to do the update. Strictly speaking, this isn't required, but is usually a good idea to make sure the trigger executes quickly and that you are only affecting rows that are being inserted or updates.

So.... you could do something like this...

CREATE TRIGGER [trg_DateTest_Nulls]
ON DateTest
FOR INSERT, UPDATE
AS
SET NOCOUNT ON

If Exists(Select * From Inserted Where StartDate = '19000101')
  Update DateTest
  Set    StartDate = NULL
  Where  StartDate = '19000101'

If there are no dates inserted or updated, then the update will not occur because of the Exists function. However, if there is a date = Jan 1, 1900, then ALL Rows in the table that have Jan 1, 1900 will be updated (not just the ones affected by this particular insert/update).

Because this is working on the entire table, I encourage you to make sure there is an index on the StartDate column, or else this trigger will be slow after you have lots of data.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Thanks guys. That's all the ammunition i needed to make my case for taking the time to build the Unique ID's into all the tables.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[ESquared]

You might also look into an INSTEAD OF trigger which would help well with INSERTS but still be a total pain for the UPDATES (without a unique key you'd have to join to the "Inserted" meta-table on every column, taking care to compare NULLs properly, besides).

But at least you might want to be aware of these kinds of triggers.