2

I have this table :

enter image description here

I want every row in FooColumn2 to be updated with the values of FooColumn1 before insert and before update In MySql i have been doing this with before insert and before update triggers , although i have read that MS-SQL doesn't support that.

How can i do that in MS-SQL ?

Alex Lemesios
  • 522
  • 9
  • 22
  • 1
    Take a look at [`INSTEAD OF Triggers`](https://technet.microsoft.com/en-us/library/ms179288(v=sql.105).aspx) for SQL Server. – Radu Gheorghiu Nov 25 '16 at 15:40

1 Answers1

2

If you just want to save the old value, just an Update trigger is required:

CREATE TRIGGER trg_iu_TableName ON TableName INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON Update t set t.Foocolumn1 = i.Foocolumn1 set t.Foocolumn2 = t.Foocolumn1 from TableName t inner join inserted i on t.idfoo=i.idfoo END

..or if you want to set Foocolumn2 to the same as Foocolumn1 on Insert:

CREATE TRIGGER trg_iu_TableName ON TableName INSTEAD OF UPDATE, INSERT AS BEGIN SET NOCOUNT ON if (select count(*) from deleted) = 0 Insert into Tablename (Foocolumn1, Foocolumn2) select Foocolumn1, Foocolumn1 from inserted else Update t set t.Foocolumn1 = i.Foocolumn1 set t.Foocolumn2 = t.Foocolumn1 from TableName t inner join inserted i on t.idfoo=i.idfoo END

cloudsafe
  • 2,444
  • 1
  • 8
  • 24
  • Doesn't [Alex Lemesios](http://stackoverflow.com/users/2952709/alex-lemesios) want to put the "old" pre-update value of `Foocolumn1` put into `Foocolumn2`, so in the `UPDATE` statement he would want `SET Foocolumn2 = t.Foocolumn1` instead of `SET Foocolumn2 = i.Foocolumn1`? – 3N1GM4 Nov 25 '16 at 16:06
  • Thank you. I have updated it to meet your interpretation. I was unsure because there was mention of 'before insert', which wouldn't make sense in this context. – cloudsafe Nov 25 '16 at 16:24
  • I still think he wants to cover off both `UPDATE` and `INSERT`, so I would still include the `INSERT` section of the trigger if it was my answer. – 3N1GM4 Nov 25 '16 at 17:08
  • I've updated is as per your suggestion. – cloudsafe Nov 25 '16 at 17:13
  • This still assumes that on `INSERT` we want to ignore any value in `Foocolumn2` and just insert the `Foocolumn1` value into both fields - the desired behaviour in this instance may be to go ahead and insert any provided `Foocolumn2` value into that field in the table, it's not clear from the question IMO. I think this does answer the question though (I have upvoted), as it shows how `INSTEAD OF` triggers can be made to emulate `BEFORE` triggers - the specifics of the behaviour can be determined by [Alex Lemesios](http://stackoverflow.com/users/2952709/alex-lemesios) when he sees this. – 3N1GM4 Nov 25 '16 at 17:17
  • Yes i want to cover up both Update and insert . Actually i want to sync the two columns . I have been trying the solution you gave above , but i get a syntax error here : set Foocolumn2 = t.Foocolumn1 . "Syntax error near "=" . I also get an other error on set Foocolumn1 = i.Foocolumn1 " The multi-part identifier "i.Foocolumn1" cannot be bound – Alex Lemesios Nov 28 '16 at 14:00
  • 1
    Alex, I have amended it. Hopefully it is fixed. – cloudsafe Nov 28 '16 at 15:42