Solved

Use trigger or stored procedure?

Posted on 2014-10-09
6
296 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
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

680 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