I need to create an after insert, update and delete trigger on the same table. Can I create one trigger that would have all three operations in it or I need to create three separate triggers? Thanks for your help.
View 3 RepliesThis isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
View 1 Replies View RelatedHello All,
I have to write Trigger for Update,
I have two tables, one is for storing records of current values, and one is for storing history of values.
How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values.
Help me.
General Problem
I need to create a set of rows every time a new row is inserted into a table.
Example (I think this would work)...
select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id)
Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values?
Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger?
Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value.
SET @ActionTaken = 'A'
IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
View 3 Replies View RelatedHi All,
I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
Thank you,
Mike
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set
(Lastname = @Lastname)
where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
View 3 Replies View Relatedl'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR insert,Update AS
IF left('Loan_No',3)='ABC'
update Test_TRG
set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF'
update Test_TRG set
loanbook = 'NEWDEF'
where loanbook is null
else
update Test_TRG
set loanbook =left('Loan_No',3)
where loanbook is null
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ".
The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if
The left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=KVS and its null then NEWKVS
left(loan_no,3)=MFS and its null then MFS
left(loan_no,3)=TCR and its null then TCR
left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained
in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it
l get an error.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR INSERT, UPDATE
AS
UPDATE Test_TRG
SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG'
WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS'
WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS'
WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 )
END
FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK
-- Fill in Primary Key or other Join Column(s)
WHERE LEFT( i.Loan_No, 3 ) = 'MCG'
OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL )
OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
Hi! i have a simple question...is possible to create a trigger who affect multiple tables?
The idea will be something like this:
create trigger mytrigger
on sales, users
as...
of course, this don´t work :)
Hey everyone...
I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...
Thank you...
I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:
CREATE Trigger upd_cost_master On tbl_cost_master
For Insert, Update As
Declare @U_Name varchar(65)
Declare @O_Name varchar(65)
Select @U_Name=I.submitted_by, @O_Name=O.submitted_by
From tbl_cost_master O Inner Join Inserted I On O.autokey = I.autokey
Print 'Inserted: ' + @U_Name
Print 'tbl_cost_master: ' + @O_Name
Commit Tran
To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?
Chris
Hi,
I'm using SQL Server 7.0. I have an insert trigger on table t05r. I populate table t05r with a DTS package. The trigger is supposed to add the record which was inserted into t05r, into t39r. After I run the DTS package to populate t05r, I checked t39r and no records where added. What is going wrong? Is it that DTS uses bulk copy and triggers don't fire on bulk copy?
Any help would be greatly appreciated.
Thanks in advance,
Darrin
I have an insert trigger on table A which inserts records into table B.
This insertion into B fails( or may fail ) at certain conditions.
I do not want the insertion into A to be affected because of this. Any ways to achieve this ?
Hi,
I have 3 tables A,B C and Triggers on each table like.
for insert/update on table 'C' calculates sum(xxfield) and updates
table 'B'.
for insert/update on table 'B' calculates sum(yyfield) and updates
table 'A'.
This works fine if I insert rows OneByOne.
Problem :- Doesn't work if I post entries(hundreds) in to table 'C' using a Query.
whats happening. Can anyone help Me????????
How do I know if the triggers on table has been disabled or not. Thanks for your ideas!!!!
View 3 Replies View RelatedHello,
I've update triggers on my table, But for some of the transactions, I don't want the triggers to fire. I know in Sybase there are set triggers off. In MS SQL, Is there any similar commands which will set triggers off for particular sql statements.
Thanks A Lot
Sejal
Hi! I am having trouble with setting triggers while doing my project. The project is on ATM machine driven through SQL. So I have Date of Transaction in one table and Account Expiry in another.
I want to set the trigger to check Account expiry when doing transaction so i wrote T-SQL
select........
getDate()-Dateoftransaction=expiry
begin '...'
end
the system saying I have got an error near the word 'begin'.
I am desperately needing help. running out of ideas.
Thanks in advance.
I have not used triggers before and wanted to know if it was approproate for my issue:
When a new user registers via the web, a record is inserted into the "student" table. I would also like to insert a record into the "StudentCourse" table that adds this new student to a class (studentID, CourseID). I thought I could look into a trigger or stored procedure for this. The problem is that I do not want to mess with the programming side of it as it is an off the shelf system and written in Java/JSP, so I would like to handle this in the DB.
Thanks in advance,
I am not actually sure if Triggers are the solution to my problem, but I have no experience with them and need help understanding if they will solve my problem.
Users that Access the database through our Front End Software can search on any field (indexed or not). For the most part, they search on indexed fields, but the problem is when they enter and empty string and attempt a search.
The Query that is passed to SQL essentially searches for everything - example below:
SELECT FieldA, FieldB, FieldC FROM Table WHERE IndexedField LIKE ''%'' ORDER BY IndexedField
The result of this is a timeout after 60 seconds and the server being practically coming to a halt during that wait. All users trying to make reads or writes are queued behind that query and it appears as though they are frozen. I have done a lot of monitoring with Profiler to figure out the problem, but I am stuck on how to stop it. The issue lies with the users and that is something I cannot fix. The Front End software cannot be modified. I cannot limit what they search on nor can I limit the fields they can search on.
Being that I can't do anything there, I was hoping I could intercept this query when SQL receives and possibly time it out faster. The queries follow a format, so I was hoping a trigger would do the trick.
Please provide any ideas or input that may help solve my problem. Also, if anybody has any good links to help me with triggers if that will take care of the problem.
EDIT: After doing some reading it is looking like triggers may not be applicable to my problem, but I am still looking for a solution.
Thanks in advance,
Don
Is Cascedaing of Triggers possible..??
(Cascading means by one trigger firing another trigger and so on..)
I am attempting to place an update trigger on a column. The following is incorrect:
CREATE TRIGGER cus_name
ON dbo.customertable
FOR UPDATE AS
IF UPDATE(cus_name)
INSERT INTO AUDITTABLE(AUD_TABLENAME,AUD_COLNAME,AUD_OLD,AUD_N EW,AUD_DATE,LANID,COMPNO)
VALUES ('customertable','cus_name',deleted.cus_name,inser ted.cus_name,GETDATE(),inserted.lanid,inserted.com pno)
Does anybody know how to do it correctly?
I came for a new assignment.
Here, we've got about 70 tables and almost each table has got 3 triggers. So, in total, we have about 200 triggers, which makes the process cumbersome.
Is there a way to get rid of these triggers?
Can we replace the trigges with something else?
or any best way to debug triggers?
Thanks for your help.
Mohan.
How do I query any data that is changed through a trigger. IN oracle you would use .new or .old to query the data. All I can find is temporary tables called Inserted and deleted but dont seem to be able to query these tables. I need to update other tables with data that was changed in the original table through a trigger.
View 3 Replies View RelatedOK guys - I'm really stumped. I've got a SQL 6.5 with an older MRP (I KNOW, it's ugly, but I can't migrate to 2000 just yet). The MRP uses triggers to accomplish a lot of things in the DB. The problem is that they were encrypted when they were created, so I can't view them in the Query tool. I thought, though, that if I did a
Code:
SELECT TEXT from syscomments C
INNER JOIN sysobjects O
ON C.ID = O.ID
WHERE NAME LIKE 'triggername'
that it would give me the encrypted (garbled) text. What I'm trying to do is run it through a decrypter because I need to see how it's processing an order so that I can remove an erroneous order from the system. My problem is that, although I know the trigger exists, when I run the above query, it produces no results. I found it odd, so I went to sysobjects and found the ID that matches the trigger name, and tried querying syscomments for all fields that match that ID - no dice. Can anyone tell me why there is no record of the trigger in syscomments?
Hi All,
I'm inserting data into a SQL Server machine which is present in my network. I need that data to be replicated using triggers to other SQl Server machine in the same/other network.
Thanks in Advance,
Aravind....
I'm fairly new to writing triggers and it's giving me a beatdown. What I'm trying to do is create a single trigger that does the following. 1) Corrects the common mistakes of our users input, which mostly consists of adding erroneous spaces. Then creating the data for two columns from the previous columns' data entered. I will also add the upper(text) later on. 2) To check to a column to verify that the entry is on our master list.
I have been able to make 1 & 2 work as separate triggers. When I try to combine them into one trigger it appears that everything runs simultaneously and gives me an error.
Can someone help?
Thanks,
JB
CREATE TRIGGER SNC ON [DATA].[a44]
FOR INSERT, UPDATE
AS
UPDATE DATA.a44
/* THIS IS PART 1 OF MY CODE */
SET COL1 = rtrim(COL1)
UPDATE DATA.a44
SET COL1 = ltrim(COL1)
UPDATE DATA.a44
SET COL2 = rtrim(COL2)
UPDATE DATA.a44
SET COL2 = ltrim(COL2)
UPDATE DATA.a44
SET COL3= rtrim(COL3)
UPDATE DATA.a44
SET COL3 = ltrim(COL3)
UPDATE DATA.a44
SET COL4 = rtrim(COL4)
UPDATE DATA.a44
SET COL4 = ltrim(COL4)
UPDATE DATA.a44
SET COL5 = rtrim(COL5)
UPDATE DATA.a44
SET COL5 = ltrim(COL5)
UPDATE DATA.a44
SET COL6 = COL2 + ' ' + COL3 + ' ' + COL4
UPDATE DATA.a44
SET COL6 = rtrim(COL6)
UPDATE DATA.a44
SET COL6 = ltrim(COL6)
UPDATE DATA.a44
SET COL7 = COL1+ ' ' + COL6+ ' ' + COL5
UPDATE DATA.a44
SET COL7 = rtrim(COL7)
UPDATE DATA.a44
SET COL7 = ltrim(COL7)
/* THIS IS PART 2 OF MY CODE */
IF (select count (*) from a37) = 0
BEGIN
IF (select count (*) from master_list, inserted
WHERE master_list.COL6 = inserted.COL6) = 0
BEGIN
RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
END
END
ELSE
IF (select count (*) from master_list, a37, inserted
WHERE master_street_list.COL6 = inserted.COL6 or a37.COL6 = inserted.COL6) = 0
BEGIN
RAISERROR ('THE NAME YOU HAVE ENTERED IS NOT IN THE MASTER LIST', 16, 1)
END
hi,
a simple question, I have this table:
-----------------------------
CREATE TABLE [dbo].[A0_INSUMOS] (
[ARTCODI] [int] NOT NULL ,
[ARTNOMB] [varchar] (80) NULL ,
[RUBCODI] [int] NULL ,
[RUBNOMB] [varchar] (40) NULL ,
[SRUBCODI] [int] NULL ,
[SRUBNOMB] [varchar] (40) NULL ,
[FAMCODI] [int] NULL ,
[FAMNOMB] [varchar] (40) NULL ,
[LINCODI] [int] NULL ,
[LINNOMB] [varchar] (40) NULL ,
[FABCOD] [int] NULL ,
[FABNOMB] [varchar] (40) NULL ,
[ARTTIP] [int] NULL ,
[ARTTIPNOMB] [varchar] (40) NULL
) ON [PRIMARY]
GO
and this table have this trigger:
-----------------------------
CREATE TRIGGER [ACTUALIZA_DIMENSION] ON [dbo].[A0_INSUMOS]
FOR INSERT, UPDATE
AS
declare @reg int
declare @nuevorub int
declare @viejorub int
declare @rubro varchar(40)
select @reg=artcodi from inserted
select @nuevorub=rubcodi from inserted
select @viejorub=rubcodi from deleted
if ( @nuevorub <> @viejorub )
begin
select @rubro=rubnomb from a1_rubros where rubcodi = @nuevorub
if @rubro is null
BEGIN
RAISERROR ('El rubro %d no existe',
16, 1, @nuevorub)
ROLLBACK TRANSACTION
END
update a0_insumos set rubnomb = @rubro where artcodi = @reg
end
-----------------------------
when I insert o update a row, work fine, but when I run a dts, the trigger not run.
Any idea?
TIA
Abel.
The other day when I was preparing for Microsoft exam 70-229 and I was reading up on triggers and I noticed that the documentation states that the default behavoir of triggers is to fire after the updateinsertdelete transaction on the table that the trigger is created on. However, I have noticed that this is not totally accurate. If you setup a trace in profiler with the with all of the xxxstarted and xxxcompleted events for the T-SQL and stored procedure event classes you will notice that the originating transaction starts, then the trigger starts, then the trigger completes and then the originating transaction completes.So after thinking about this for a while, I have to come to the conclusion that sql server must consider the trigger as part of the orignating transaction, and must therefore complete the trigger before the originating transaction commits. With this in mind, should'nt the default behavoir of triggers be considered "during" and not "after".
View 2 Replies View Related