Solved

Use trigger or stored procedure?

Posted on 2014-10-09
6
307 Views
Last Modified: 2014-10-09
Hi guys,

I have a boolean field in a Sql Server table and and if that value is updated I need to copy the change into a boolean field in a seperate database. I assume triggers would be the way forward, but if they error i wont get any feedback that the error occured. So should I use a stored procedure and where would it sit? I can see that triggers actually sit on the table itslef but stored procedures sit outside of specific tables?

Im new to both triggers and stored procedures.

Thanks,
Dean
0
Comment
Question by:deanlee17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40370277
If you want to automatically update another table based on the updates in the current table then the on insert, update trigger is the best way..

Not sure what you mean by getting error trigger will run the SQL statement that you give to it if the statement is faulty it wont run if not it will update the table. If you want to catch the error on the update of the source table then you need to catch it before the trigger since the error catching is not possible in the trigger.

Related to your question "where the trigger sits and where the procedure sits"? The triggers are bound to table and they monitor the change of the data and then execute the queries while the procedures are unbound to any table and they are run on any table with any type of queries and procedures enable you to monitor the execution of the queries.

Hope it helps..
0
 

Author Comment

by:deanlee17
ID: 40370283
Yes this helps alot thank you. Could you provide a rough template of the SQL required on the trigger?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40370295
here is the example of trigger :

IF OBJECT_ID('TRG_TEST') IS NOT NULL
DROP TRIGGER TRG_TEST
GO

CREATE TRIGGER TRG_TEST
ON dbo.TABLE_NAME
AFTER INSERT AS
BEGIN
INSERT INTO TABLE_NAME_BACKUP
SELECT * FROM INSERTED
END
GO

Open in new window

0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:deanlee17
ID: 40370302
Few questions below...

IF OBJECT_ID('TRG_TEST') IS NOT NULL  <-- Assuming this is run first, why would it even be null?
DROP TRIGGER TRG_TEST <-- What is this doing? looks to me like this runs if the trigger is not null?
GO

CREATE TRIGGER TRG_TEST
ON dbo.TABLE_NAME 
AFTER INSERT AS
BEGIN
INSERT INTO TABLE_NAME_BACKUP
SELECT * FROM INSERTED  <-- This I assume is where we set the value of the second table, how do we actually 
                                                       call the value of the first table to insert into here? It's a boolean so 1 or 0
END
GO

Open in new window

0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40370307
Ok..

IF OBJECT_ID('TRG_TEST') IS NOT NULL  <-- This is to check if it exist and then in the next it wil replace it
DROP TRIGGER TRG_TEST <-- Based on the above line if the trigger exists it will be droped  and then below code wil create it with same name you can run it without this part code
GO

CREATE TRIGGER TRG_TEST
ON dbo.TABLE_NAME
AFTER INSERT AS
BEGIN
Update  TABLE_NAME_BACKUP
SELECT column_name FROM INSERTED  <-- Inserted is special (internal) table which holds all the data that is inserted i.e. you columns which are inserted so you can rewrite it to suites you.. It is the temp table . Your secon able should go instead of TABLE_NAME_BACKUP value and
END
GO

Here is more appropriate code..

CREATE TRIGGER TRI_UPDATE_SECOND_TABLE ON dbo.FIRST_TABLE
    AFTER UPDATE
AS
    IF UPDATE(COLUMN_NAME)
    BEGIN
        UPDATE c
            SET COLUMN_NAME = i.COLUMN_NAME
        FROM dbo.SECOND_TABLE AS c
          JOIN inserted AS i
            ON i.ID = c.ID     -- use the relevant column for joining
                ) ;
    END ;

Open in new window

0
 

Author Comment

by:deanlee17
ID: 40370342
Thats great much appreciated
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question