SQL 2008 Trigger



If I need to create a trigger to run the insert below every time an insert is done to GSDatabase how would I go about it..

Basically if and insert is done to GSDatabase
run this Query which checks if it exist in the table insert
the insert Inserts into AccessData
INSERT INTO AccessData.dbo.Product( ItemNumber,ItemType,VendorNo ,ProductLine,Category,ItemStatus,Entry_Date,Special_Pricing,
Contract_Item,Parent_Item,PackageCount,Replacement, Notes )
select  ItemNumber, CI.ItemType, CI.PrimaryVendorNo, CI.ProductLine, Left(GP.ItemNumber,2), 'OK', CONVERT (date, SYSDATETIME()), 'N', 'N', NULL,NULL,NULL,NULL
from GSDatabase.dbo.Product GP left outer join SQLMAS90.dbo.CI_Item CI on GP.ItemNumber = CI.ItemCode 
where GP.ItemNumber not in (Select AP.ItemNumber from AccessData.dbo.Product AP)
and len(gp.ItemNumber)>3

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
 
Chris LuttrellSenior Database ArchitectCommented:
Try a trigger like this on the Product table in GSDatabase, you can name the trigger what ever you like.
USE GSDatabase
GO

CREATE TRIGGER dbo.Product_Insert_AccessData
ON dbo.Product
FOR INSERT
AS
BEGIN
	INSERT INTO AccessData.dbo.Product( ItemNumber,ItemType,VendorNo ,ProductLine,Category,ItemStatus,Entry_Date,Special_Pricing,
	Contract_Item,Parent_Item,PackageCount,Replacement, Notes )
	select  ItemNumber, CI.ItemType, CI.PrimaryVendorNo, CI.ProductLine, Left(GP.ItemNumber,2), 'OK', CONVERT (date, SYSDATETIME()), 'N', 'N', NULL,NULL,NULL,NULL
	from Inserted GP left outer join SQLMAS90.dbo.CI_Item CI on GP.ItemNumber = CI.ItemCode 
	where GP.ItemNumber not in (Select AP.ItemNumber from AccessData.dbo.Product AP)
	and len(gp.ItemNumber)>3
	
END


GO

Open in new window

0
 
k_murli_krishnaCommented:
There are DDL and DML triggers. You need DML trigger and a BEFORE INSERT trigger if you have recursion i.e. within trigger you are inserting into same table on whose INSERT INTO the trigger is defined. If this is not the case, then AFTER INSERT trigger is better. Connect to OR use GSDatabase

CREATE TRIGGER DoInsert
ON DATABASE AFTER INSERT
AS
INSERT INTO AccessData.dbo.Product( ItemNumber,ItemType,VendorNo ,ProductLine,Category,ItemStatus,Entry_Date,Special_Pricing,
Contract_Item,Parent_Item,PackageCount,Replacement, Notes )
select  ItemNumber, CI.ItemType, CI.PrimaryVendorNo, CI.ProductLine, Left(GP.ItemNumber,2), 'OK', CONVERT (date, SYSDATETIME()), 'N', 'N', NULL,NULL,NULL,NULL
from GSDatabase.dbo.Product GP left outer join SQLMAS90.dbo.CI_Item CI on GP.ItemNumber = CI.ItemCode
where GP.ItemNumber not in (Select AP.ItemNumber from AccessData.dbo.Product AP)
and len(gp.ItemNumber)>3
GO

If you do not have both databases in same server/instance, then you have to setup database link using this example:

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO

--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]

i.e. a 4 part name like linkedserverlogin.database_name.dbo/schema.object_name

0
 
LowfatspreadCommented:
you probably want to actually do this in at least two steps to give yourself some application resilience in case your accessdata database is unavailable...

consider having the trigger insert the data into a local database table for the gsd database first and having a job  update the
the accessdata database at regular intervals...

coukd you explain the background to your application in more detail as cross database maintenance is often a tricky situation...


0
 
Leo TorresSQL DeveloperAuthor Commented:
Lowfatspread:
Well GSD is more like the data dump given to us by or vendor.. There is not integrity to those tables... The reason for the insert is to add new items to the transaction database if  they dont exist there already..

As far as the temp table then insert not a bad idea.. may not be needed in this case GSD data usually not uploaded 4 times a year every Quarter..
but i will take it into account


k_murli_kris:
Thanks for the tip but yes but databases are on the same server.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.