Solved

Use trigger or stored procedure?

Posted on 2014-10-09
6
278 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now