CODEWITHSUNDEEP
sqlsql-servertriggerssql-update
Web Xpert
Web Xpert

Reputation: 21

MSSQL - trigger to disable old records beeing updated

I have large system coded in PHP (ages ago) - it runs queries on MSSQL database. What I need is to create a trigger on a database level, that will disable any update statement to proceed if the update was going to change the column f_month to 99 (ie. contains update [table] set f_month='99') on a row that has my_date <= [24months before now or older]. I expect this should be feasible, but have no luck trying to figure out how on my own.. thanks for any suggests, Jan

-------------- working for one row updates, but fails on multiople row update --------------

CREATE TRIGGER update_fix
    ON  mytable
    FOR UPDATE AS
BEGIN
    SET NOCOUNT ON; 
    if (select f_month from inserted)=99 and (select x_date from mytable where data_id in((select data_id from inserted)))<=DateAdd(yy,-2,GetDate())
    BEGIN
          RAISERROR('Cannot update old records',16,1)
          ROLLBACK TRANSACTION
          RETURN;
    END
END 
GO

The above shown solution works just fine if one row is beeing updated, however it does not work for updates affecting multiple row..and that is a problem..can anyone help me changing the trigger to work for updates affecting multiple rows? Should examine each row beeing updated separately - so if there is an update of 10 rows where one of them should not be updated - just that one will not be updated, other rows will be updated sucessfully..Is that even possible with a trigger?

Upvotes: 0

Views: 96

Answers (1)

Harish U
Harish U

Reputation: 89

Try Check constraint,that might also help .

Upvotes: 0

Related Questions