I have a vb program that scrapes data out of a text file into a sql server 2000 table called Orderlog. What I would like to do is have an insert trigger on Orderlog that searches for all orders that have not been updated (updated is a field set to 0 by default and then set to 1 after it has been updated by an insert trigger on the table it inserted data into, I have the update part working).
The problem I have run into is that I need multiple inserts to run. I have order tables that must be fed from the orderlog table. They are called ISREPAIR, ISZOO, ISCLASS. They each have data that is specific to their type of order. And they are defined in the Orderlog table under the [Proc] column. I am currently able to do the entire process with one order type. Example:
CREATE TRIGGER trigger_Insert_Data ON [dbo].[Orderlog]
FOR INSERT
AS
INSERT INTO ISREPAIR
(Order_Num, Hospital_ID, Telephone, Comment1, Comment2, Mnemonic, Entered_From, Given_To, Requesting_Location, Number_Of_Cables,
User_Name, Request_Type_Mnemonic, Request_Type_Name, Problem_Type_Mnemonic, Problem_Type_Name, Building, Floor_Suite, Comment3,
Comment4, Comment5, Comment6, Sys_Telephone_Ext, Entered_By, Requesting_Name, Entered_Mnemonic)
SELECT Orderlog.[Order], Orderlog.hospitalID, Orderlog.ctcTel, Orderlog.comment1, Orderlog.comment2, Orderlog.Mnemonic, Orderlog.enteredFromDev,
Orderlog.assignToIts, Orderlog.Dept, Orderlog.numOfCables, Orderlog.userName, Orderlog.probMnemonic1, Orderlog.probName1,
Orderlog.probMnemonic2, Orderlog.probName2, Orderlog.bldg, Orderlog.flrSte, Orderlog.comment3, Orderlog.comment4, Orderlog.comment5,
Orderlog.comment6, Orderlog.sysTelExt, Orderlog.enteredByName, Orderlog.reqLocName, Orderlog.userMnemonic
FROM Orderlog LEFT OUTER JOIN
ISREPAIR ON Orderlog.[Order] = ISREPAIR.Order_Num
WHERE (Orderlog.updated = 0) AND (Orderlog.[Proc] = 'ISREPAIR')
This works fine, but I need an IF statement or CASE, something that will do the inserts for each different type of order, something like this:
CREATE TRIGGER trigger_Insert_Data ON [dbo].[Orderlog]
FOR INSERT
AS
IF (SELECT [Proc] FROM ORDERLOG WHERE Updated = 0) = 'ISREPAIR'
Begin
INSERT INTO ISREPAIR
(Order_Num, Hospital_ID, Telephone, Comment1, Comment2, Mnemonic, Entered_From, Given_To, Requesting_Location, Number_Of_Cables,
User_Name, Request_Type_Mnemonic, Request_Type_Name, Problem_Type_Mnemonic, Problem_Type_Name, Building, Floor_Suite, Comment3,
Comment4, Comment5, Comment6, Sys_Telephone_Ext, Entered_By, Requesting_Name, Entered_Mnemonic)
SELECT Orderlog.[Order], Orderlog.hospitalID, Orderlog.ctcTel, Orderlog.comment1, Orderlog.comment2, Orderlog.Mnemonic, Orderlog.enteredFromDev,
Orderlog.assignToIts, Orderlog.Dept, Orderlog.numOfCables, Orderlog.userName, Orderlog.probMnemonic1, Orderlog.probName1,
Orderlog.probMnemonic2, Orderlog.probName2, Orderlog.bldg, Orderlog.flrSte, Orderlog.comment3, Orderlog.comment4, Orderlog.comment5,
Orderlog.comment6, Orderlog.sysTelExt, Orderlog.enteredByName, Orderlog.reqLocName, Orderlog.userMnemonic
FROM Orderlog LEFT OUTER JOIN
ISREPAIR ON Orderlog.[Order] = ISREPAIR.Order_Num
WHERE (Orderlog.updated = 0) AND (Orderlog.[Proc] = 'ISREPAIR')
END
ELSE IF (SELECT [Proc] FROM ORDERLOG WHERE Updated = 0) = 'ISZOO'
Begin
INSERT INTO ISZOO
(Order_Num, Hospital_ID, Telephone, Comment1, Comment2, Entered_From, Given_To, Requesting_Location, Telephone, Entered_By,
Requesting_Name, Location, Entered_From, Device_Type, Contact_Name, Problem_Dept, Requesting_Mnemonic, Contact_Mnemonic, Request_Name)
SELECT Orderlog.[Order], Orderlog.hospitalID, Orderlog.ctcTel, Orderlog.comment1, Orderlog.comment2, Orderlog.enteredFromDev, Orderlog.assignToIts,
Orderlog.Dept, Orderlog.sysTelExt, Orderlog.enteredByName, Orderlog.reqLocName, Orderlog.reqLocName, Orderlog.enteredFromDev,
Orderlog.devType, Orderlog.ctcName, Orderlog.probDept, Orderlog.Mnemonic, Orderlog.userMnemonic, Orderlog.reqTypeName
FROM Orderlog LEFT OUTER JOIN
ISZOO ON Orderlog.[Order] = ISZOO.Order_Num
WHERE (Orderlog.updated = 0) AND (Orderlog.[Proc] = 'ISZOO')
End
BUT, I have tried doing this many different ways and can't get it to work. Thanks for any help you can throw my way!
The problem I have run into is that I need multiple inserts to run. I have order tables that must be fed from the orderlog table. They are called ISREPAIR, ISZOO, ISCLASS. They each have data that is specific to their type of order. And they are defined in the Orderlog table under the [Proc] column. I am currently able to do the entire process with one order type. Example:
CREATE TRIGGER trigger_Insert_Data ON [dbo].[Orderlog]
FOR INSERT
AS
INSERT INTO ISREPAIR
(Order_Num, Hospital_ID, Telephone, Comment1, Comment2, Mnemonic, Entered_From, Given_To, Requesting_Location, Number_Of_Cables,
User_Name, Request_Type_Mnemonic, Request_Type_Name, Problem_Type_Mnemonic, Problem_Type_Name, Building, Floor_Suite, Comment3,
Comment4, Comment5, Comment6, Sys_Telephone_Ext, Entered_By, Requesting_Name, Entered_Mnemonic)
SELECT Orderlog.[Order], Orderlog.hospitalID, Orderlog.ctcTel, Orderlog.comment1, Orderlog.comment2, Orderlog.Mnemonic, Orderlog.enteredFromDev,
Orderlog.assignToIts, Orderlog.Dept, Orderlog.numOfCables, Orderlog.userName, Orderlog.probMnemonic1, Orderlog.probName1,
Orderlog.probMnemonic2, Orderlog.probName2, Orderlog.bldg, Orderlog.flrSte, Orderlog.comment3, Orderlog.comment4, Orderlog.comment5,
Orderlog.comment6, Orderlog.sysTelExt, Orderlog.enteredByName, Orderlog.reqLocName, Orderlog.userMnemonic
FROM Orderlog LEFT OUTER JOIN
ISREPAIR ON Orderlog.[Order] = ISREPAIR.Order_Num
WHERE (Orderlog.updated = 0) AND (Orderlog.[Proc] = 'ISREPAIR')
This works fine, but I need an IF statement or CASE, something that will do the inserts for each different type of order, something like this:
CREATE TRIGGER trigger_Insert_Data ON [dbo].[Orderlog]
FOR INSERT
AS
IF (SELECT [Proc] FROM ORDERLOG WHERE Updated = 0) = 'ISREPAIR'
Begin
INSERT INTO ISREPAIR
(Order_Num, Hospital_ID, Telephone, Comment1, Comment2, Mnemonic, Entered_From, Given_To, Requesting_Location, Number_Of_Cables,
User_Name, Request_Type_Mnemonic, Request_Type_Name, Problem_Type_Mnemonic, Problem_Type_Name, Building, Floor_Suite, Comment3,
Comment4, Comment5, Comment6, Sys_Telephone_Ext, Entered_By, Requesting_Name, Entered_Mnemonic)
SELECT Orderlog.[Order], Orderlog.hospitalID, Orderlog.ctcTel, Orderlog.comment1, Orderlog.comment2, Orderlog.Mnemonic, Orderlog.enteredFromDev,
Orderlog.assignToIts, Orderlog.Dept, Orderlog.numOfCables, Orderlog.userName, Orderlog.probMnemonic1, Orderlog.probName1,
Orderlog.probMnemonic2, Orderlog.probName2, Orderlog.bldg, Orderlog.flrSte, Orderlog.comment3, Orderlog.comment4, Orderlog.comment5,
Orderlog.comment6, Orderlog.sysTelExt, Orderlog.enteredByName, Orderlog.reqLocName, Orderlog.userMnemonic
FROM Orderlog LEFT OUTER JOIN
ISREPAIR ON Orderlog.[Order] = ISREPAIR.Order_Num
WHERE (Orderlog.updated = 0) AND (Orderlog.[Proc] = 'ISREPAIR')
END
ELSE IF (SELECT [Proc] FROM ORDERLOG WHERE Updated = 0) = 'ISZOO'
Begin
INSERT INTO ISZOO
(Order_Num, Hospital_ID, Telephone, Comment1, Comment2, Entered_From, Given_To, Requesting_Location, Telephone, Entered_By,
Requesting_Name, Location, Entered_From, Device_Type, Contact_Name, Problem_Dept, Requesting_Mnemonic, Contact_Mnemonic, Request_Name)
SELECT Orderlog.[Order], Orderlog.hospitalID, Orderlog.ctcTel, Orderlog.comment1, Orderlog.comment2, Orderlog.enteredFromDev, Orderlog.assignToIts,
Orderlog.Dept, Orderlog.sysTelExt, Orderlog.enteredByName, Orderlog.reqLocName, Orderlog.reqLocName, Orderlog.enteredFromDev,
Orderlog.devType, Orderlog.ctcName, Orderlog.probDept, Orderlog.Mnemonic, Orderlog.userMnemonic, Orderlog.reqTypeName
FROM Orderlog LEFT OUTER JOIN
ISZOO ON Orderlog.[Order] = ISZOO.Order_Num
WHERE (Orderlog.updated = 0) AND (Orderlog.[Proc] = 'ISZOO')
End
BUT, I have tried doing this many different ways and can't get it to work. Thanks for any help you can throw my way!