Menu
I have a "InsertTime" field in a table in a SQL Server 2005 database which is defaulted to "getDate()" when the record is first inserted into the database. I want to ensure that this column is not updated again.
Can this column be set to readonly or is there a better way to do this without writing all of the sql for the developers?
998
Grant SELECT/INSERT/UPDATE/DELETE (as needed) on the view to APP . Create an INSTEAD OF trigger on the view to translate inserts, updates and deletes to the underlying table. The PL/SQL in the trigger can ignore that column for updates, or raise an exception if you want it to detect an attempt to modify the column.
Indicates whether you can tab to a browse column but not edit it.
Make Database Read/Write If you face an error that if the database is already in use, you can resolve the same by making database in single user mode – here is the guideline SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
You can implement a 'read-only' field by creating an UPDATE trigger that checks for updates to that column and then rolls them back.
IF EXISTS (SELECT name FROM sys.objects
WHERE name = 'ReadOnlyInsertTime_tr' AND type = 'TR')
DROP TRIGGER dbo.ReadOnlyInsertTime_tr;
GO
CREATE TRIGGER ReadOnlyInsertTime_tr
ON dbo.MyTable
AFTER UPDATE
AS
IF (UPDATE(InsertTime))
BEGIN
ROLLBACK
-- Raise an informative error
-- RAISERROR (50009, 16, 10)
END;
GO
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
