SQL trigger, check if certain value was entered
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Now what to check:
If inserted value1 = null, change it to 0
How to do it via trigger? I googled for examples and I have never ever done a trigger, so it is rather confusing.
So far got only this:
CREATE TRIGGER testTrigger
ON myTable
AFTER INSERT, UPDATE, DELETE
You can add default value . This is how it's done for a new column. For existing one you should add constraint. Check Update 2
ALTER TABLE table_name
ADD column1 int NOT NULL DEFAULT(0)
Add a column with a default value to an existing table in SQL Server
UPDATE:
To set default value, you should update NULL values at first.
UPDATE table_name
SET column1 = 0
WHERE column1 IS NULL
UPDATE 2:
Try adding constraint
ALTER TABLE table_name
ADD CONSTRAINT DF_column1 DEFAULT 0 FOR column1
You could write this in the trigger:
UPDATE T SET value1 =0
FROM table_name T
JOIN INSERTED I
ON T.<id>=I.<id>
WHERE I.value1 is null
INSERTED table which is accessible only within trigger will store the values that have inserted..
对INSERTED值使用ISNULL
SELECT ISNULL(INSERTED,0) FROM INSERTED
链接地址: http://www.djcxy.com/p/24714.html
下一篇: SQL触发器,检查是否输入了某个值