%
main()
Sub main()
%>
<% = AppTitle %>
<%
Dim Query, Content, Content2
Query = "SELECT name FROM sysobjects WHERE (xtype = 'U') AND (status >= 0) AND name <> 'TC1システムM' ORDER BY name"
' Query = "SELECT name FROM sysobjects WHERE (status >= 0) ORDER BY name"
Set db = Server.CreateObject("ADODB.Connection")
db.Open SQLConn
Dim hh, ii, jj
Set Content = OpenQuery(Query)
Dim HtmlAll, HtmlRow, bFalse, HtmlFalse, WriteRow
Dim ScriptFalse1, ScriptFalse2, ScriptFalse3, ScriptFalse4
Dim bTableFalseFlg
bTableFalseFlg = 0
Dim UpdateTrigger(10, 10), DeleteTrigger(10), ParentTable(10), ParentTalbeIndex(10, 10)
Dim FieldName, Attributes
ParentTable(1) = "TC1システムM"
ParentTable(2) = "TC2演習M"
ParentTable(3) = "TC3学生グループM"
ParentTable(4) = "TGA入出庫TRN"
ParentTable(5) = "TGB銀行口座F"
ParentTable(6) = "TSA仕入売上書類F"
ParentTable(7) = "TSC運送書類F"
ParentTable(8) = "TSC運送費請求書手動F"
ParentTable(9) = "TSK伝票F"
ParentTable(0) = "TDA会社M"
For ii = 0 To 9
Query = "SELECT * FROM " & ParentTable(ii) & " WHERE 1 = 0 "
Set Content2 = OpenQuery(Query)
For jj = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(jj).Name
Attributes = Content2.Fields(jj).Attributes
If Attributes = 8 And jj <= 9 Then
ParentTalbeIndex(ii, jj) = FieldName
End If
Next
Next
Dim bFlg
Do While Not(Content.BOF Or Content.EOF)
Dim name , TableFieldName
name = Content.Fields("name")
If InStr(name, "TGB銀行口座") > 0 Then
TableFieldName = "銀行CD"
Else
TableFieldName = "会社CD"
End If
Dim ttype
If name = "TC2演習M" Or name = "TC3学生グループM" Or name = "TC9区分M" Or name = "TC9文書種類M" Or name = "TC9名刺デザインM" Or name = "TTM名刺データF" Then
ttype = 1
ElseIf name = "TC4演習許可学生グループM" Or name = "TC6演習履歴M" Or name = "TDA会社M" Or name = "TDA勘定科目M" Or name = "TDA市場M" Or name = "TDA商品M" Or name = "TDA報告書書式M" Then
ttype = 2
ElseIf name = "TC5学生M" Then
ttype = 3
ElseIf name = "TGA入出庫明細TRN" Then
ttype = 4
ElseIf name = "TGB銀行口座入出金TRN" Then
ttype = 5
ElseIf name = "TSA仕入売上書類明細F" Then
ttype = 6
ElseIf name = "TSC運送書類明細F" Then
ttype = 7
ElseIf name = "TSC運送費請求書手動明細F" Then
ttype = 8
ElseIf name = "TSK伝票仕入売上明細F" OR name = "TSK伝票明細F" Then
ttype = 9
Else
ttype = 0
End If
For hh = 0 To 10
If ParentTalbeIndex(ttype, hh) <> "" Then
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " UPDATE " & name & "" & "
" & vbCrLf
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " SET"
If name = "TGB銀行口座F" And ParentTalbeIndex(ttype, hh) = "会社CD" Then
TableFieldName = "銀行CD"
Else
TableFieldName = ParentTalbeIndex(ttype, hh)
End If
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " " & name & "." & TableFieldName & " = inserted." & ParentTalbeIndex(ttype, hh) & "" & "
" & vbCrLf
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " FROM " & name & "" & "
" & vbCrLf
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " INNER JOIN deleted ON "
For ii = 0 To 10
If ParentTalbeIndex(ttype, ii) <> "" Then
If ii = 0 Then
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " "
Else
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " AND"
End If
If name = "TGB銀行口座F" And ParentTalbeIndex(ttype, ii) = "会社CD" Then
TableFieldName = "銀行CD"
Else
TableFieldName = ParentTalbeIndex(ttype, ii)
End If
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " " & name & "." & TableFieldName & " = deleted." & ParentTalbeIndex(ttype, ii) & "" & "
" & vbCrLf
End If
Next
If ttype = 1 Then
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " CROSS JOIN inserted" & "
" & vbCrLf
Else
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " INNER JOIN inserted ON "
bFlg = 0
For ii = 0 To 10
If ParentTalbeIndex(ttype, ii) <> "" AND ii <> hh Then
If bFlg = 0 Then
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " "
Else
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " AND"
End If
bFlg = 1
If name = "TGB銀行口座F" And ParentTalbeIndex(ttype, ii) = "会社CD" Then
TableFieldName = "銀行CD"
Else
TableFieldName = ParentTalbeIndex(ttype, ii)
End If
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " " & name & "." & TableFieldName & " = inserted." & ParentTalbeIndex(ttype, ii) & "" & "
" & vbCrLf
End If
Next
End If
UpdateTrigger(ttype, hh) = UpdateTrigger(ttype, hh) & " " & "
" & vbCrLf
End If
Next
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " DELETE FROM " & name & "" & "
" & vbCrLf
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " FROM " & name & "" & "
" & vbCrLf
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " INNER JOIN deleted ON "
bFlg = 0
For ii = 0 To 10
If ParentTalbeIndex(ttype, ii) <> "" Then
If ii = 0 Then
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " "
Else
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " AND"
End If
If name = "TGB銀行口座F" And ParentTalbeIndex(ttype, ii) = "会社CD" Then
TableFieldName = "銀行CD"
Else
TableFieldName = ParentTalbeIndex(ttype, ii)
End If
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " " & name & "." & TableFieldName & " = deleted." & ParentTalbeIndex(ttype, ii) & "" & "
" & vbCrLf
End If
Next
DeleteTrigger(ttype) = DeleteTrigger(ttype) & " " & "
" & vbCrLf
bTableFalseFlg = 0
Content.MoveNext
Loop
Dim tmp
For ttype = 0 To 9
For hh = 0 To 9
If UpdateTrigger(ttype, hh) <> "" Then
tmp = "CREATE TRIGGER ""GTR_" & ParentTable(ttype) & "_" & ParentTalbeIndex(ttype, hh) & "_更新トリガ"" ON " & ParentTable(ttype) & " FOR UPDATE AS" & "
" & vbCrLf & "SET NOCOUNT ON" & "
" & vbCrLf & "IF "
tmp = tmp & "UPDATE(" & ParentTalbeIndex(ttype, hh) & ")"
tmp = tmp & "" & "
" & vbCrLf & " BEGIN" & "
" & vbCrLf & UpdateTrigger(ttype, hh) & "
" & vbCrLf & " END" & "
" & vbCrLf & "GO" & "
" & vbCrLf & "
" & vbCrLf
UpdateTrigger(ttype, hh) = tmp
Response.Write(UpdateTrigger(ttype, hh))
End If
Next
DeleteTrigger(ttype) = "CREATE TRIGGER ""GTR_" & ParentTable(ttype) & "_削除トリガ"" ON " & ParentTable(ttype) & " FOR DELETE AS" & "
" & vbCrLf & "SET NOCOUNT ON" & "
" & vbCrLf & DeleteTrigger(ttype) & "
" & vbCrLf & "GO" & "
" & vbCrLf & "
" & vbCrLf
Response.Write(DeleteTrigger(ttype))
Next
%>
<%
End Sub
%>