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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.