添加列后如何立即更新数据 [英] How to update data immediatly after column has been added

问题描述

我有一个任务:

创建SQL脚本以将列添加到数据库并立即更新.但是此脚本可以启动一次以上-因此仅在第一次时才需要执行此脚本(添加和更新).

这是我的解决方案:

I''ve a task:

Create SQL script to add the column to the database and update it immediately. But this script could be started more then one time - so it is required to this (add and update) only first time.

Here is my solution:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	ALTER TABLE [OmniCalc].[dbo].[Table1]
	ADD [PlateDistance] decimal(18,0) default 0 NOT NULL;
	
	UPDATE [OmniCalc].[dbo].[Table1]
	SET [PlateDistance] = [bauteilAbstand] / 2
END

GO

但结果我得到了:

消息207，第16级，状态1，第13行
无效的列名"PlateDistance".

我尝试做一些更改以允许GO批处理，但是在这种情况下，无法避免脚本按方案覆盖用户数据的情况:

1.启动脚本

2.用户更改一些数据

3.再次启动脚本

But in result i get:

Msg 207, Level 16, State 1, Line 13
Invalid column name ''PlateDistance''.

I''ve tried to do some changes to allow GO batch processing, but in this case it is impossible to avoid situation when scripts overwrite user data by scenario:

1. Start script

2. User changes some data

3. Start script again

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	ALTER TABLE [dbo].Table1
	ADD [PlateDistance] decimal(18,0) default 0 NOT NULL;
	
	Select [PlateDistance] from [dbo].Table1
END

GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	UPDATE [dbo].Table1
	SET [PlateDistance] = [bauteilAbstand] / 2
END

GO

即使我将变量添加到构造中，也没有用，因为变量在GO运算符后丢失.

And even if I add variable to the construction - It does not help because variable is loss after GO operator.

DECLARE @created BIT;

SET @created = 0;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance')
BEGIN
	ALTER TABLE [dbo].Table1
	ADD [PlateDistance] decimal(18,0) default 0 NOT NULL;
	
	Select [PlateDistance] from [dbo].Table1
	
	SET @created = 1;
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'Table1' AND COLUMN_NAME = 'PlateDistance') AND @created = 1
BEGIN
	UPDATE [dbo].Table1
	SET [PlateDistance] = [bauteilAbstand] / 2
END

GO

所以.问题-如何仅在第一个脚本运行时才添加列并立即更新其数据?

So. Question - how to ADD column and UPDATE its data immediately only on the first script run?

推荐答案

使用Sql触发器...

Use Sql Triggers...

delimiter

创建 TRIGGER stu_insert 之后插入 打开 stu_table FOR 每行 开始 插入 插入 stu_log(用户ID，说明) 值(用户()，CONCAT(' 插入学生记录'， new.stu_id，' '，new.stu_name，' '，new.stu_class))； END

CREATE TRIGGER stu_insert AFTER insert ON stu_table FOR EACH ROW BEGIN INSERT into stu_log(user_id, description) VALUES (user(), CONCAT('Insert Student record ', new.stu_id,' ',new.stu_name,' ',new.stu_class)); END

定界符；

这篇关于添加列后如何立即更新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！