desiredforsome
asked on
Simple SQL Insert Trigger
I have a database with account numbers in it.
I wan to create a trigger that upon insert of that table it sends all those accounts numbers to another db on the same server.
Then I want the new DB to have a trigger on it that adds feilds to another table in the same DB when inserted using the account number that has come in.
For instance
DB - A table B gets account number 1234 inserted into it.
Trigger runs and inserts 1234 into DB - B - Table A
Trigger on DB B sees it has an insert and then runs an insert statement on table forms in DB B . Using the account 1234.
I wan to create a trigger that upon insert of that table it sends all those accounts numbers to another db on the same server.
Then I want the new DB to have a trigger on it that adds feilds to another table in the same DB when inserted using the account number that has come in.
For instance
DB - A table B gets account number 1234 inserted into it.
Trigger runs and inserts 1234 into DB - B - Table A
Trigger on DB B sees it has an insert and then runs an insert statement on table forms in DB B . Using the account 1234.
Last Comment
"I wan to create a trigger that upon insert of that table it sends all those accounts numbers to another db on the same server."
I believe it is wrong to rely on the SQL Database backend to populate multiple tables with data inserted into a single table and in my opinion (and hopefully many others) your UI / APP should INSERT/DELETE/UPDATE all necessary tables with the data as it should be stored in the database and not to rely on hidden SQL objects to do that for you. All you need is the appropriate connections and statements for this to work.
http://programmers.stackexchange.com/questions/171024/never-do-in-code-what-you-can-get-the-sql-server-to-do-well-for-you-is-this
I believe it is wrong to rely on the SQL Database backend to populate multiple tables with data inserted into a single table and in my opinion (and hopefully many others) your UI / APP should INSERT/DELETE/UPDATE all necessary tables with the data as it should be stored in the database and not to rely on hidden SQL objects to do that for you. All you need is the appropriate connections and statements for this to work.
http://programmers.stackexchange.com/questions/171024/never-do-in-code-what-you-can-get-the-sql-server-to-do-well-for-you-is-this
I believe it is wrong to rely on the SQL Database backend to populate multiple tables with data inserted into a single table and in my opinion (and hopefully many others) your UI / APP should INSERT/DELETE/UPDATE all necessary tables with the data as it should be stored in the database and not to rely on hidden SQL objects to do that for you.Sorry but I can't agree with you. Business model should always kept in the databases and a trigger isn't an hidden SQL object. It's more easy to change a trigger or stored procedure than an application code. Also, let the servers do the heaviest work and not the clients.
ASKER
I am new at writing triggers so A good example would be appreciated. The reason I cannot do this from the UI. is becuase I am writing an interface to the software and the software is 3rd party in which I cannot access anything to code to sql other than creating a trigger in the db itself.
My interface has its own DB it relies on but needs some data populated into it from the 3rd party db.
THe instance is the same its runnning on a sql express.
Just need the account number that was inserted in to be inserted into my db from 3rd party.
THank You
My interface has its own DB it relies on but needs some data populated into it from the 3rd party db.
THe instance is the same its runnning on a sql express.
Just need the account number that was inserted in to be inserted into my db from 3rd party.
THank You
Your trigger would be something like in the pseudo code below:
CREATE TRIGGER [TI_TableB] ON DB_A.[dbo].[TableB]
AFTER INSERT
AS
BEGIN
-- stop the recursive trigger from firing more than once ... comment it out if you have bulk insert
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
RETURN
END
IF EXISTS (SELECT account_number FROM inserted)
INSERT INTO DB_B.[dbo].[TableA] (account_number, 'rest of the columns here from the table definition',...)
SELECT account_number, 'rest of the columns here from the table definition',...
FROM inserted
END
GO
CREATE TRIGGER [TI_TableB] ON DB_A.[dbo].[TableB]
AFTER INSERT
AS
BEGIN
-- stop the recursive trigger from firing more than once ... comment it out if you have bulk insert
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
RETURN
END
IF EXISTS (SELECT account_number FROM inserted)
INSERT INTO DB_B.[dbo].[TableA] (account_number, 'rest of the columns here from the table definition',...)
SELECT account_number, 'rest of the columns here from the table definition',...
FROM inserted
END
GO
ASKER
Would "inserted" by DB-A or DB-B?
ASKER
SEem to be getting error saying that xrefid is not permitted in this context.
Please tell me if this trigger looks ok of if it has issues and what they may be.
Please tell me if this trigger looks ok of if it has issues and what they may be.
CREATE TRIGGER INSERTTABLEPORTAL ON outlookreport.dbo.MFUSERS
AFTER INSERT
AS
BEGIN
-- stop the recursive trigger from firing more than once ... comment it out if you have bulk insert
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
RETURN
END
IF EXISTS (SELECT xrefid FROM outlookreport.dbo.requestdoc)
INSERT INTO outlookreport.dbo.requestdoc values(xrefid,'test','Y','N')
SELECT xrefid,requested,status,required
FROM outlookreport.dbo.requestdoc
END
GO
ASKER
I have got the trigger to work kind of. It however is inserted it everytime. It does not just insert if not exists. I want it to only insert the inserted data if it does not exists.
Well, your query says to insert when exists. You miss a NOT there:
IF NOT EXISTS (SELECT xrefid FROM outlookreport.dbo.requestd
IF NOT EXISTS (SELECT xrefid FROM outlookreport.dbo.requestd
ASKER
Here is my trigger and it is not inserting when changed. Any ideas? I know its something small
USE [outlookreport]
GO
/****** Object: Trigger [dbo].[XREFID] Script Date: 11/14/2014 09:26:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[XREFID]
ON [outlookreport].[dbo].[MFUSERS]
AFTER INSERT
AS
DECLARE @XREFID NVARCHAR(50)
SET @XREFID = (SELECT TOP 1 XREFID FROM OUTLOOKREPORT.DBO.MFUSERS)
BEGIN
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
RETURN
END
IF NOT EXISTS(SELECT XREFID FROM OUTLOOKREPORT.DBO.REQUESTDOC)
INSERT INTO OUTLOOKREPORT.DBO.REQUESTDOC (XREFID)
SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS
END
ASKER
THis also not working
USE [outlookreport]
GO
/****** Object: Trigger [dbo].[XREFID] Script Date: 11/14/2014 09:26:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[XREFID]
ON [outlookreport].[dbo].[MFUSERS]
AFTER INSERT
AS
BEGIN
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
RETURN
END
IF NOT EXISTS(SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS)
INSERT INTO OUTLOOKREPORT.DBO.REQUESTDOC (XREFID)
SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS
END
I can't understand the logic.
If NOT EXISTS(SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS)
INSERT INTO OUTLOOKREPORT.DBO.REQUESTD
SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS --> If no records exists this won't work at all !!!!
If NOT EXISTS(SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS)
INSERT INTO OUTLOOKREPORT.DBO.REQUESTD
SELECT XREFID FROM OUTLOOKREPORT.DBO.MFUSERS --> If no records exists this won't work at all !!!!
ASKER
What I want it to do is look to see if the record that was inserted into MFUSERS exists in REQUESTDOC if it does then it does nothing. If it does not exists in REQUEST doc then it inserts the XREFID from MFUSERS that was inserted into REQUESTDOC.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your patience this is wonderful.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
If the DB's are in the same instance you just need to give the full reference to the objects (DB.Schema.Object):
Open in new window
If there's no transformations or any other operations than simple inserts you can use a transactional replication instead of the triggers.