Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Use trigger or stored procedure?

Posted on 2014-10-09
6
Medium Priority
?
323 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

579 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