Solved

Use trigger or stored procedure?

Posted on 2014-10-09
6
311 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

635 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