I have a process I have inherited that requires me to create an insert statement, but the kicker is that I will not know the temp table's name ahead of time until it has been created. Now my code works as is, but it complains about the syntax and I was hoping there might be a better way of doing this. The code I would like to change is in orange.
Anyway, any advice would be greatly appreciated...
Here is the basic trigger:
Code Snippet
ALTER TRIGGER [dbo].[trg_DownloadDataTypes_ins] ON [dbo].[DownloadDataTypes]
INSTEAD OF INSERT
AS
SET NOCOUNT OFF
DECLARE @.TableName varchar(100),
@.FileType varchar(100),
@.FileName varchar(100),
@.CampusID varchar(15),
@.DateCreated DateTime,
@.ParentID int
IF (SELECT COUNT(FileName) FROM INSERTED) = 1
BEGIN
SELECT @.TableName = (SELECT [FileName] FROM INSERTED)
SELECT @.FileName = (SELECT [FileName] FROM INSERTED)
SELECT @.FileType = (SELECT FileType FROM INSERTED)
SELECT @.CampusID = (SELECT CampusID FROM INSERTED)
SELECT @.DateCreated = (SELECT DateDownLoaded FROM INSERTED)
IF @.TableName = 'SyncTest-Deleteme'
BEGIN
DELETE FROM DownloadDataTypes WHERE [FileName] = 'SyncTest-DeleteMe'
END
ELSE
BEGIN
/*Inserting an IF/ELSE statement here dependant on @.FileType to seperate the handling of 'CAMPUS INVENTORY' file types.
IF @.FileType = 'CAMPUS INVENTORY'
BEGIN
DECLARE @.Qry varchar(4000)
--First, insert the record into tblSyncedInventory.
INSERT INTO tblSyncedInventory([FileName], FileType, CampusID, DateCreated)
SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED
SET @.ParentID = @.@.IDENTITY
--Now, go out and get the child records from their temp table, insert them into tblSyncedInventoryDetails, then drop the temp table.
SET @.Qry = 'INSERT INTO tblSyncedInventoryDetails (FileID, ISBN, Copies, Accession, DateCreated, FileName) SELECT ' + str(@.ParentID) + ', dbo.[' + @.TableName + '].ISBN, dbo.[' + @.TableName + '].Copies, NULL, GETDATE(), dbo.[' + @.TableName + '].FileName FROM dbo.[' + @.TableName + ']'
EXEC (@.Qry)
--Now, drop the temp table.
SET @.Qry = 'DROP TABLE [' + @.TableName + ']'
EXEC (@.Qry)
END
ELSE
BEGIN
INSERT INTO dbo.tblSyncedInventory([FileName],[FileType],[CampusID],[DateCreated]) VALUES (@.TableName, @.FileType, @.CampusID, @.DateCreated)
INSERT INTO tblDownloadDataTypes ([FileName], FileType, CampusID, DateCreated) SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED
SET @.ParentID = @.@.IDENTITY
EXEC stpro_ProcessPDAFileFromTrigger @.TableName, @.FileType, @.CampusID, @.ParentID
END
What error(s) are you receiving?
|||I think you're missing one or more END statements. Each BEGIN should have a matching one
ALso, you can grab all the variables from INSERTED In one query:
SELECT @.TableName =[FileName],
@.FileName = [FileName],
@.FileType = FileType,
@.CampusID = CampusID,
@.DateCreated = DateDownLoaded
FROM INSERTED
Not sure about how temporary these temp tables are either, but without knowing the app or the rest of the code its difficult to suggest anything constructive.
HTH
sql