Excel中用宏批量整理SQL脚本，生成ORACLE触发器语句(二)
m(i)
fieldStr = fieldStr + vbCrLf + clsField.fieldName
Next i
printFields = fieldStr
End Function
Function getTrgInsert(ByRef aCollection As Collection, ByVal destDb As String, ByVal tableName As String) As String
Dim i As Integer
Dim clsField As ClassField
Dim trgSQL As String
Dim trgWhere As String
Dim fieldSQl As String
Dim valuesSQL As String
Dim sufExt As String
sufExt = ":NEW"
trgWhere = ""
trgSQL = "CREATE OR REPLACE TRIGGER TRG_" & tableName & "_INS" & vbCrLf & _
" AFTER Insert " & vbCrLf & _
" ON " & tableName & vbCrLf & _
" FOR EACH ROW " & vbCrLf & _
" DECLARE v_count NUMBER;" & vbCrLf & _
" BEGIN "
For i = 1 To aCollection.Count
Set clsField = aCollection.Item(i)
If trgWhere = "" Then
trgWhere = clsField.fieldName + "=:NEW." + clsField.fieldName
fieldSQl = clsField.fieldName
valuesSQL = sufExt + "." + clsField.fieldName
Else
trgWhere = trgWhere + " and " + clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
fieldSQl = fieldSQl + "," + clsField.fieldName
valuesSQL = valuesSQL + "," + sufExt + "." + clsField.fieldName
End If
Next i
trgSQL = trgSQL & vbCrLf & " SELECT COUNT(*) INTO v_count FROM " + tableName + "@" + destDb + " WHERE " + trgWhere + ";"
trgSQL = trgSQL & vbCrLf & " IF v_count = 0 THEN "
trgSQL = trgSQL & vbCrLf & " insert into " + tableName + "@" + destDb + "(" + fieldSQl + ") values (" + valuesSQL + ");"
trgSQL = trgSQL & vbCrLf & " end if;"
trgSQL = trgSQL & vbCrLf & " END; "
getTrgInsert = trgSQL
End Function
Function getTrgUpdate(ByRef aCollection As Collection, ByVal destDb As String, ByVal tableName As String) As String
Dim i As Integer
Dim clsField As ClassField
Dim trgSQL As String
Dim trgWhere As String
Dim fieldSQl As String
Dim valuesSQL As String
Dim sufExt As String
Dim sufExtOld As String
sufExt = ":NEW"
sufExtOld = ":OLD"
trgWhere = ""
trgSQL = "CREATE OR REPLACE TRIGGER TRG_" & tableName & "_UPT" & vbCrLf & _
" AFTER update " & vbCrLf & _
" ON " & tableName & vbCrLf & _
" FOR EACH ROW " & vbCrLf & _
" DECLARE v_count NUMBER;" & vbCrLf & _
" BEGIN "
For i = 1 To aCollection.Count
Set clsField = aCollection.Item(i)
If trgWhere = "" Then
trgWhere = clsField.fieldName + "=" + sufExtOld + "." + clsField.fieldName
fieldSQl = clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
Else
trgWhere = trgWhere + " and " + clsField.fieldName + "=" + sufExtOld + "." + clsField.fieldName
fieldSQl = fieldSQl + "," + clsField.fieldName + "=" + sufExt + "." + clsField.fieldName
End If
Next i
trgSQL = trgSQL & vbCrLf & " SELECT COUNT(*) INTO v_count FROM " + tableName + "@" + destDb + " WHERE " + trgWhere + ";"
trgSQL = trgSQL & vbCrLf & " IF v_count >
0 THEN "
trgSQL = trgSQL & vbCrLf & " update " + tableName + "@" + destDb + " set " + fieldSQl + " WHERE " + trgWhere + ";"
trgSQL = trgSQL & vbCrLf & " end if;"
trgSQL = trgSQL & vbCrLf & " END; "
getTrgUpdate = trgSQL
End Function
Function getTrgDelete(ByRef aCollection As Collection, ByVal destDb As String, ByVal tableName As String) As String
Dim i As Integer
Dim clsField As ClassField
Dim trgSQL As String
Dim trgWhere As String
Dim fieldSQl As String
Dim valuesSQL As String
Dim sufExt As String
Dim sufExtOld As String
sufExt = ":NEW"
sufExtOld = ":OLD"
trgWhere = ""
trgSQL = "CREATE OR REPLACE TRIGGER TRG_" & tableName & "_DEL" & vbCrLf & _
" AFTER delete " & vbCrLf & _
" ON " & tableName & vbCrLf & _
" FOR EACH ROW " & vbCrLf & _
" DECLARE v_count NUMBER;" & vbCrLf & _
" BEGIN "
For i = 1 To aCollection.C