Another Trigger Question....

[JasSim]

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!

 

[swampBoogie]

You can use the table inserted, which contain all data being inserted.

CREATE TRIGGER trigger_Insert_Data ON dbrderlog
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 [Order], hospitalID, ctcTel, comment1, comment2,
Mnemonic, enteredFromDev, assignToIts, Dept, numOfCables,
userName, probMnemonic1, probName1, probMnemonic2,
probName2, bldg, flrSte, comment3,
comment4, comment5, comment6, sysTelExt, enteredByName,
reqLocName, userMnemonic
FROM inserted
WHERE Orderlog.[Proc] = 'ISREPAIR'
and [Order] not in (select order_num from ISREPAIR)

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 [Order], hospitalID, ctcTel, comment1, comment2,
enteredFromDev, assignToIts, Dept, sysTelExt,
enteredByName, reqLocName, reqLocName, enteredFromDev,
devType, ctcName, probDept, Mnemonic, userMnemonic, reqTypeName
FROM inserted
WHERE Orderlog.[Proc] = 'ISZOO'
and [Order] not in (select order_num from ISZOO)

-- insert into ISCLASS

 

[JasSim]

Ok, that works for one insert statement, but I am back to the problem of not being able to handle both insert statements together... It needs to be an if or something that is selective.

Thanks!

 

[swampBoogie]

I don't see why you should need an if. If there is no data with ISREPAIR in the Proc column nothing will get inserted into the ISREPAIR table.
Btw, I noticed an error in my code. It said

WHERE Orderlog.[Proc] = 'ISZOO'

which is invalid. It should just be

WHERE [Proc] = 'ISZOO'

as orderlog is not involved in the query. I.e.


CREATE TRIGGER trigger_Insert_Data ON dbrderlog
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 [Order], hospitalID, ctcTel, comment1, comment2,
Mnemonic, enteredFromDev, assignToIts, Dept, numOfCables,
userName, probMnemonic1, probName1, probMnemonic2,
probName2, bldg, flrSte, comment3,
comment4, comment5, comment6, sysTelExt, enteredByName,
reqLocName, userMnemonic
FROM inserted
WHERE [Proc] = 'ISREPAIR'
and [Order] not in (select order_num from ISREPAIR)

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 [Order], hospitalID, ctcTel, comment1, comment2,
enteredFromDev, assignToIts, Dept, sysTelExt,
enteredByName, reqLocName, reqLocName, enteredFromDev,
devType, ctcName, probDept, Mnemonic, userMnemonic, reqTypeName
FROM inserted
WHERE [Proc] = 'ISZOO'
and [Order] not in (select order_num from ISZOO)

 

[JasSim]

Ya I caught that too. Thanks! What happens is that the vb program gets caught in a loop trying to update or something and it appears that the table or row is locked. I took off the update trigger that updated the row saying that it had been inserted into the appropriate table. So, what now... Let me know if you have any ideas. Thanks!