October 16, 2014 at 1:46 am
Hi guys I need to create an instead of trigger which prevents inserting the year of 2009,2010,2011 . For instance, i have a table like this;
create table dummy(date_c datetime,name varchar(40))
Transactions should not insert 2009,2010 and 2011 to date_c column..
This is what i have so far... But it says invalid column name date_c when i want to execute it. I can get select from dummy table tho.
CREATE TRIGGER trgHistory ON dummy
INSTEAD OF Insert,update,delete
AS
begin
BEGIN TRAN
SET NOCOUNT ON
if(date_c>'2011-01-01')
begin
RAISERROR('Cannot Insert where date < 2011-01-01',16,1); ROLLBACK; end
end
October 16, 2014 at 3:12 am
INSTEAD OF triggers run instead of the code that fired the trigger, which means that the table is not touched unless explicitly defined in the trigger code. I don't see such code in the sample you posted.
You don't want an INSTEAD OF trigger, you want an AFTER trigger here. The ROLLBACK command will handle unwanted values correctly.
You cannot refrence a column like that, but you have to read from the inserted logical table.
There is no need to fire the trigger for DELETE operations, as they won't change the value of date_c.
Here's what the trigger should look like:
CREATE TRIGGER trgHistory
ON dummy
FOR Insert,update
AS
BEGIN
BEGIN TRAN
SET NOCOUNT ON
IF EXISTS (
SELECT *
FROM inserted
WHERE date_c>'2011-01-01'
)
BEGIN
RAISERROR('Cannot Insert where date < 2011-01-01',16,1);
ROLLBACK;
END
END
However, the best way to avoid unwanted data in a column is a CHECK constraint.
ALTER TABLE dummy ADD CONSTRAINT ck_2011 CHECK (date_c > '20110101')
If your table already contains unwanted data, the constraint will check only new values.
If your table doesn't contain unwanted data, you can check the constraint:
ALTER TABLE dummy CHECK CONSTRAINT ck_2011
Hope this helps
-- Gianluca Sartori
October 16, 2014 at 4:37 am
Thank you so much !!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply