CODEWITHSUNDEEP
sqlsql-serversql-server-2008
HShbib
HShbib

Reputation: 1841

SQL: Trigger is not performing

I have created a trigger to set the value of a column of the data type time to null when the time inserted is Less than GETDATE() 

ALTER trigger [dbo].[NULL_TIME_TRIGGER]
  on [dbo].[Products]
  after insert
  as
  update Products
  set ParkingStartTime = NULL
  from Products
   join inserted i
     on i.ParkingStartTime  = Products.ParkingStartTime 
  where i.ParkingStartTime < CAST(GETDATE() AS TIME);

The problem is that when I select * From table the record is still having the time rather than being NULL.

Any Ideas ?

Regards.

Upvotes: 0

Views: 92

Answers (4)

Andriy M
Andriy M

Reputation: 77677

I have an idea, yes. When the row is inserted, the time value is greater than the current time so it's left untouched. When you are checking the contents of the table, some values are already past the current time at the moment of checking.

Upvotes: 0

M.R.
M.R.

Reputation: 4827

I think in end, you need a sql job, that just looks through and sets the time to null when there time criteria is less than getDate()

Upvotes: 1

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

rsenna has one option, and it is superior in many respects. Here is how I would play your AFTER INSERT version (asssume int as PK):

ALTER trigger [dbo].[NULL_TIME_TRIGGER]       
ON [dbo].[Products]       
AFTER INSERT       
AS

DECLARE @ID int

SELECT @ID = ID FROM INSERTED

UPDATE Products
SET ParkingStartTime = NULL
WHERE ID = @ID

Upvotes: 0

rsenna
rsenna

Reputation: 11963

I sincerely don't know whats wrong with your trigger, it should be working AFAIK.

But this is definitely not the way to go. You seem to be trying to validate some inserted values. If that's the case, you should be using an INSTEAD OF INSERT trigger, not an AFTER INSERT, like you're currently doing.

So it would be like this:

ALTER trigger [dbo].[NULL_TIME_TRIGGER]
  on [dbo].[Products]
  instead of insert
  as
  begin
      insert into Products (
          field1, field2, ..., fieldn, ParkingStartTime
      ) select
          field1, field2, ..., fieldn,
          case when ParkingStartTime < CAST(GETDATE() AS TIME) then NULL else ParkingStartTime end
      from
          inserted
  end
  go

That way SQL Server will intercept all inserts into the Products table, and will only execute the insert inside the trigger, which fixes the ParkingStartTime value. So the trigger doesn't need to execute another update to make them right.

Hope it helps.

Upvotes: 0

Related Questions