ERWIN脚本

ERWIN脚本好像是基于文本式的脚本语言，未找到直接定义变量的方法。目前的解决方法是，有需要用到变量的代码点，再次执行生成变量值的脚本。因为是基于文本的脚本，所以脚本语法可以随意嵌套，如判断不存在某个表字段，然后做一些处理的脚本可以这样写：

%If(%!=(%ForEachColumn(Parent,","){%If(%==(%ColName,CancelJudge)){NotRealDelete}},NotRealDelete)))
{
DELETE FROM  dbo.%TableName
WHERE
%ForEachColumn(Parent," AND ")
{
%If(%ColIsPK)
{
%ColName = @%ColName}}
}

 

========== 表新增、删除、修改的脚本示例 ==========

【TableName_Delete】

 

%ForEachTable()  {
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[%TableName_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[%TableName_Delete]
go

CREATE PROCEDURE  dbo.%TableName_Delete
(
%ForEachColumn(Parent,",")
{
 %If(%ColIsPK)
{
 @%ColName %ColumnDatatype}}
)
AS

%ForEachColumn(Parent,",")
{
%If(%==(%ColName,CancelJudge))
{
UPDATE  dbo.%TableName SET
CancelJudge = 1
WHERE
%ForEachColumn(Parent," AND ")
{
%If(%ColIsPK)
{
%ColName = @%ColName}}
}}

%If(%!=(%ForEachColumn(Parent,","){%If(%==(%ColName,CancelJudge)){NotRealDelete}},NotRealDelete)))
{
DELETE FROM  dbo.%TableName
WHERE
%ForEachColumn(Parent," AND ")
{
%If(%ColIsPK)
{
%ColName = @%ColName}}
}

go

}

 

【TableName_Insert】

%ForEachTable()  {
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[%TableName_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[%TableName_Insert]
go

CREATE PROCEDURE  dbo.%TableName_Insert
(
%ForEachColumn(Parent,",")
{
%If(%!=(%Substr(%AttNullOption,1,8),IDENTITY))
{
%If(%!=(%ColName,LastChanged)){@%ColName %ColumnDatatype}}},
%ForEachColumn(Parent,",")
{
%If(%==(%Substr(%AttNullOption,1,8),IDENTITY)){@newId %ColumnDatatype output,}
}@newLastChanged timestamp output
)
AS
INSERT INTO  dbo.%TableName
(%ForEachColumn(Parent,",")
{
 %If(%And(%!=(%Substr(%AttNullOption,1,8),IDENTITY),%!=(%ColName,LastChanged)))
 {%ColName}}
)
VALUES
(%ForEachColumn(Parent,",")
{
 %If(%And(%!=(%Substr(%AttNullOption,1,8),IDENTITY),%!=(%ColName,LastChanged)))
 {@%ColName}}
)
 
  %ForEachColumn (Parent,","){
     %If(%ColIsPK)
     {
        %If(%!=(%Substr(%AttNullOption,1,8),IDENTITY)) 
        {
        Select @newLastChanged = LastChanged From  dbo.%TableName Where %ColName = @%ColName
        }
        %Else
        {
        Select @newId = %ColName, @newLastChanged = LastChanged From  dbo.%TableName Where %ColName = SCOPE_IDENTITY()}
     }
  }

go
}

 

【TableName_Update】

%ForEachTable()  {

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[%TableName_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[%TableName_Update]
go

CREATE PROCEDURE  dbo.%TableName_Update
(
%ForEachColumn(Parent,","))
{
@%ColName %ColumnDatatype},
@newLastChanged timestamp output
)
AS
UPDATE  dbo.%TableName SET
%ForEachColumn(Parent,",")
{
%If(%And(%Not(%ColIsPK),%!=(%ColName,LastChanged)))
{
%ColName = @%ColName}}
WHERE
%ForEachColumn(Parent," AND ")
{
%If(%ColIsPK)
{
%ColName = @%ColName}} AND LastChanged=@lastChanged

IF @@ROWCOUNT = 0
    RAISERROR('Row has been edited by another user', 16, 1)             
 
SELECT @newLastChanged = LastChanged
FROM %TableName  WHERE %ForEachColumn(Parent,",")
{
%If(%ColIsPK)
{
%ColName = @%ColName}}
RETURN

go
}

 

============= 加入时间戳的旧Logic方式脚本 ================

【Insert】

%ForEachTable()  {
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[%TableNameInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[%TableNameInsert]
go

CREATE PROCEDURE  dbo.%TableNameInsert
(
 %ForEachColumn(Parent,",")
{
 %If(%!=(%Substr(%AttNullOption,1,8),IDENTITY))
 {@%ColName %ColumnDatatype}}
)
AS
INSERT INTO  dbo.%TableName
(
 %ForEachColumn(Parent,",")
{
 %If(%!=(%Substr(%AttNullOption,1,8),IDENTITY))
 {%If(%!=(%ColName,LastChanged)){%ColName}}}
)
VALUES
(
 %ForEachColumn(Parent,",")
{
 %If(%!=(%Substr(%AttNullOption,1,8),IDENTITY))
 {%If(%!=(%ColName,LastChanged)){@%ColName}}}
)
 %ForEachColumn (Parent,","){
      %If(%ColIsPK) {
           %If(%==(%Substr(%AttNullOption,1,8),IDENTITY)){
        Select %ColName %ForEachColumn(Parent," AND ")
{
%If(%==(%ColName,LastChanged))
{,LastChanged}} From dbo.%TableName Where %ColName = @@IDENTITY}
       }}
go
}

【Update】

%ForEachTable()  {

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[%TableNameUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[%TableNameUpdate]
go

CREATE PROCEDURE  dbo.%TableNameUpdate
(
%ForEachColumn(Parent,","))
{
@%ColName %ColumnDatatype}
)
AS
UPDATE  dbo.%TableName SET
%ForEachColumn(Parent,",")
{
%If(%And(%Not(%ColIsPK),%!=(%ColName,LastChanged)))
{
%ColName = @%ColName}}
WHERE
%ForEachColumn(Parent," AND ")
{
%If(%ColIsPK)
{
%ColName = @%ColName}} %ForEachColumn(Parent," AND ")
{
%If(%==(%ColName,LastChanged))
{
AND LastChanged=@LastChanged

IF @@ROWCOUNT = 0
    RAISERROR('Row has been edited by another user', 16, 1)             
SELECT LastChanged
FROM dbo.%TableName  WHERE %ForEachColumn(Parent," AND ")
{
%If(%ColIsPK)
{
%ColName = @%ColName}}
RETURN
}}
go
}