CODEWITHSUNDEEP
sqlsql-serverdatabase-trigger
Messi
Messi

Reputation: 51

SQL Trigger update/Insert

I'm creating a table and setting up a trigger to insert/update field_3 using field_1 and 2. See my example below.

ID | Start_Date | End_Date | Period |
1    3/10/17      3/20/17      10
2    2/05/17      3/10/17       5

Trigger

ALTER TRIGGER [dbo].[tr_insert_Period]
  ON  [dbo].[MEDICAL_EXCUSE]
  for update
AS 
BEGIN

  SET NOCOUNT ON;

declare @Start_Date Datetime
declare @End_Date  Datetime
declare @Period Float

set @Start_Date = ( select me_start_date from inserted)
set @End_Date = ( select ME_End_Date from inserted)
set @Period  = ( select Period  from inserted)

update MEDICAL_EXCUSE
set @Period  = DATEDIFF(day,@Start_Date , @End_Date)

END

it won't trigger just work if you execute Manually.

Any help Much Appreciated.

Upvotes: 0

Views: 791

Answers (2)

HLGEM
HLGEM

Reputation: 96640

Several issues I see with your trigger. First you can never assume only one record is going to be updated ever. Enterprise databases (typically the only kind complex enough to need triggers) often are accessed outside the user application and a large insert might appear.

Next, you want to get the records from an insert, so it needs to be an insert trigger. And finally you are updating the value of the variable @period instead of the field Period in your table.

Try this:

ALTER TRIGGER [dbo].[tr_insert_Period]
  ON  [dbo].[MEDICAL_EXCUSE]
  for insert
AS 
BEGIN

UPDATE ME
SET Period  = DATEDIFF(day,i.Start_Date , i.End_Date)
FROM MEDICAL_EXCUSE ME
JOIN Inserted i on i.id = me.id

END

You might also want this to work for updates as well, so then make it: 

FOR Insert, update

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Don't use a trigger. Just use a computed column. I think this is what you want:

alter table KOPS_MEDICAL_EXCUSE
    add period as (DATEDIFF(day, me_start_date, me_end_date));

Note that datediff() returns an integer, so there is no reason to declare anything as a float.

With a computed field, the value is usually calculated when you query the table. You can materialize the value if you want using the PERSISTED keyword.

Upvotes: 3

Related Questions