0

Trying to create a trigger just for a specific column however can only get a trigger for a whole table to work.

  Create Trigger Nameblock on tblcustomer
  For Insert
  As
  Begin
  rollback transaction
  print 'Name edit not allowed!'
  End

I want it so it only creates the trigger if an update to CustomerName is done which is a column in the table

user2437672
  • 69
  • 1
  • 7
  • possible duplicate of [MySQL Trigger question: only trigger when a column is changed?](http://stackoverflow.com/questions/4097949/mysql-trigger-question-only-trigger-when-a-column-is-changed) – Liron Apr 22 '15 at 17:21
  • I've seen this but the NEW and OLD command doesn't seem to work for me. – user2437672 Apr 22 '15 at 17:22
  • That trigger will not not work on mysql, and more over provide some more information on that you are trying to achieve. – Abhik Chakraborty Apr 22 '15 at 17:22
  • I'm trying to block any attempt of anyone updating (changing) the Customername column in tblcustomer table. – user2437672 Apr 22 '15 at 17:24
  • 1
    Instead of OLD and NEW, sql server uses INSERTED and DELETED. – Tab Alleman Apr 22 '15 at 17:40
  • 1
    Triggers fire when an event happens, like INSERT. If you want logic to only work when a certain column is modified that belongs inside your trigger. Please realize that your code is an INSERT trigger which means that there is no update here like you stated in your original post. – Sean Lange Apr 22 '15 at 18:21

2 Answers2

1
Create Trigger Nameblock on tblcustomer
For Update
As
if UPDATE(name)
Begin
  rollback transaction
  RAISERROR('Name edit not allowed!',16,1)
End

to cover commented point you can use below trigger too.

Create Trigger Nameblock on tblcustomer
INSTEAD OF UPDATE
As
Begin
  UPDATE tblcustomer set phone=I.phone,address=I.adress 
  from INSERTED I inner join tblcustomer on (tblcustomer.id=I.id)
  where I.name=tblcustomer.name

  if UPDATE(name)
      print 'Name edit not allowed!'
END
Mohsen
  • 79
  • 5
  • 1
    Be aware however, that this does rollback the entire transaction, so that if they changed anything esle, it will also get rolled back. It also means that if they changed 400 records with one query and only one had a name change the whole transaction is rolled back. – HLGEM Apr 22 '15 at 20:38
  • 1
    that's a good point I think you could solve these problems with INSTEAD OF UPDATE trigger and then you do not need to use rollback transaction, you can just can ignore rows that have name column changed – Mohsen Apr 23 '15 at 06:11
  • 1
    Another issues is that customer names actually do change. People get married or divorced, companies get bought out or change their names. How are you going to handle that if a trigger will never allow anyone to change the names? A trigger affects all changes to data. Is this something better done by the application? Why is the field even editable on the application? – HLGEM Apr 23 '15 at 13:18
0

Looks like you are pretty close. Adding IF UPDATE (CustomerName) should do it:

CREATE TRIGGER Nameblock ON tblcustomer
FOR UPDATE --change this to update instead of insert
AS
BEGIN
    IF UPDATE (CustomerName)  --Add this line to your query
    BEGIN
    rollback transaction
    print 'Name edit not allowed!'
    END 
END
GO
FutbolFan
  • 13,235
  • 3
  • 23
  • 35