deanlee17
asked on
Use trigger or stored procedure?
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
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
ASKER
Yes this helps alot thank you. Could you provide a rough template of the SQL required on the trigger?
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats great much appreciated
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..