MS SQL Server :: Insert / Update Trigger Based On A Column's Value

SQL 2012 :: Trigger After UPDATE INSERT Based On Column Value

Jul 18, 2014

I would like to change a value after the table updates inserts a new row based on the value of that column

Table Name is: MPanel
Col Names: RID, FPID.

I want to:

If column RID='16'
Then UPDATE FPID='1'

How can I trigger that update? Would this work:???

CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END

View 9 Replies View Related

Trigger To Update Table Based On COUNT Of Column

Sep 26, 2007

Hello again,

I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.

Code Snippet
CREATE TABLE #Assignment
(CallID INT IDENTITY(1500,1) PRIMARY KEY,
AssignmentGroup VARCHAR(25),
Assignee VARCHAR(25)
)
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')

GO

SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee

What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.

So, the logic for the trigger would be

UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))

But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.

Does any one have an idea or pointers as to how I should go about this?

Grateful for any advice, thanks
matt

View 5 Replies View Related

How To Open A Socket Connection Based On An Insert TRIGGER?

Mar 19, 2007

Hello,Would it be possible to open a socket connection and send few data based on an insert TRIGGER?My client would basically like to send the ID of a product via socket connection when a new product is inserted into the SQL database.Thanks for your helpArnold

View 13 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008

Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Update Column Value In Whole Database (based On Column Value)?

Aug 27, 2015

How to Update Column Value in the whole data base (based on Column Value)?

View 2 Replies View Related

Insert/Update From ASP.NET And Trigger.

Aug 23, 2007

Hi,
Is it possible to group all my update/insert (one table) ?
Cause I hope to have all the update/insert in one Trigger execution. I need to sum up some figures here.
I am using ASP.NET 2.0, SQLOLEDB and SQL 2005.
Please Help and Thank you.

View 8 Replies View Related

Insert Trigger With An Update

Apr 25, 2002

I would like to update a record in a table when a record is added. The dilema is I want to update the record I am adding. I tried an insert trigger ( it doesn't fail, it just doesn't update) If I use the same syntax in TSQL after the record has been inserted it handles the update properly. SO I think the syntax is OK. I have the trigger below. The question is - Is there a way to do this?
Thanks

CREATE TRIGGER transaction_ins_tr
ON dbo.transactions
FOR INSERT
AS
DECLARE @pat_id varchar(5),
@location_name varchar(50),
@account_id char(12),
@tran_num int
--get patient id
SELECT @pat_id = patient_id
FROM inserted
select @account_id = account_id from dbo.patient pa
where pa.patient_id = @pat_id
--get location
select @location_name = df_sitename from
development..patient_ dp
where dp.account_id = @account_id
-- update trans location
update dbo.transactions
set df_sitename = @location_name
where tran_num = @tran_num

View 1 Replies View Related

Insert/update Trigger

Jan 19, 2004

Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record).
tbl1 = tblallBag_data
tbl2 = tblBag_data
tbl3 = tblShipping_sched

I created a trigger in tbl1 to insert a record into tbl2 and it works fine.

CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data
FOR INSERT
AS

INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity)
SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity
FROM inserted

How can I update tbl2?
Should I create another trigger to update tbl2?
Should I join the two tbls(tbl2 & tbl3) to find
@work_ord_num = work_ord_num , @work_ord_line_num = work_ord_line_num

Thanks for your help!

View 1 Replies View Related

Trigger To Update On Insert?

Oct 31, 2013

I have several tables that i need to add a trigger to. One such table is "Clusters". This table contains a column called ClusterFKey which should be the same value as the AutoID column in the Serverdata table (same db) and should be populated every time a record is added to the Clusters table. The value should be joined on the servername column's value in both the Serverdata and Clusters table.

So, when a record is added to Clusters, the ClusterFkey should equal the AutoID key in Serverdata where Clusters.servername = Serverdata.server and Clusters.ClusterFkey is null.

View 10 Replies View Related

Update/insert Trigger

Jul 20, 2005

Hi!I wonder how to use conditions in the inserted table(in ainsert/update) trigger? The inserted table contain all the rows thathave been updated or inserted (for an update/insert trigger), but outof all these rows in inserted table, I only want the rows where aparticular field have been updated, for example if idkey have beenupdated it would be in inserted BUT I only want this row if the fieldamount have been updated. Can a use the UPDATE(column) some how? Anyideas?/Jenny

View 2 Replies View Related

SQL Insert/Update Trigger

May 2, 2007

I would like to insert an update trigger which should achieve the following functionallity (as per trigger algorithm) but somehow it is not working. can you please help me since I am still green with SQL and programming, but I need to solve this before I can continue!

CREATE TRIGGER ActiveCard

ON AccessControl

FOR Insert (when i insert a new row in Access Control)

AS UPDATE UniqueCard

( to update the matching (match IDNum) row within UniqueCard table)

Set UniqueCard.Active = Inserted.Active (card and active are of the same type)

Where UniqueCard.IDNum = AccessControl.IDNum

Thanks

View 6 Replies View Related

Insert Data In Update Trigger

Nov 24, 2003

I have a table called drugs with a cost, date, and drugname field. I have a history table that I want to insert into anytime the fields are changed in the drugs table. I want to capture the old data along with the new cost. is that possible in a update trigger? What I have below doesn't like when it comes to setting the new cost

create trigger Updt_drugs on drugs for update as
-- updates record with sql user and timestamp
--created 1-12-02 tim cronin
declare @newcost money
begin
set @newcost = select cost from inserted
insert into drugsold ([oldcost],[cost],[cdate],[drug])
select [cost],@newcost,[cdate],[drug]
from deleted dt
end

View 1 Replies View Related

TRIGGER FOR INSERT,UPDATE,DELETE

Aug 15, 2001

I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.

DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH

View 2 Replies View Related

Trigger Insert Record On Update

Jul 20, 2004

I have a parent table with 27 Columns and Child Table with 37 colums - when even there is an update in any of the columns on Parent or Child table, I require new record inserted into Audit_Parent and Audit_child table. Please help with
SQL Code on Create Trigger and insert records into Audit_parent and Audit_child when an Update occurs on any of the columns.
Insert into AuditParent and AuditChild should occur whenever there is an update on either Parent or child table.

Thanks

:confused:

View 1 Replies View Related

Trigger To Insert Or Update 2nd Table

Mar 11, 2004

I am new to triggers and need help on the following:

I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row.

Thanks for any suggestions...

Alan

View 4 Replies View Related

T-SQL (SS2K8) :: After Insert Or Update Trigger

Jul 11, 2014

I currently have a AFTER UPDATE trigger that looks at a specific column being updated. It works great, but I am now needing to also capture the field data when it is initially created as well. Do I need to build a sperate FOR INSERT trigger to capture the initial column data when inserted, or is it possible to build it into my current AFTER UPDATE trigger?

Here is my current trigger:
CREATE TRIGGER [dbo].[xResponsible_By] ON [dbo].[PARTS_REQUESTOR]
AFTER UPDATE

[code]....

View 2 Replies View Related

Insert Update Delete Trigger?

Dec 2, 2014

I have to create a trigger that will log who changed information on a table and when (NOT what they have changed).

My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.

Here is the SQL I have so far.

-- Primary Database Tables --
CREATE TABLE Physician (
Physician_ID int not null identity(1,1) primary key,
First_Name varchar(100),
Last_Name varchar(100),
Mobile_Number varchar(15),
Pager_Number varchar(15)

[code].....

View 1 Replies View Related

Insert And Update Trigger On Same Table

Jul 23, 2005

I currently have 2 tables as follows:CREATE TABLE [CRPDTA].[F55MRKT119](mhan8 int,mhac02 varchar(5),mhmot varchar(5),mhupmj int)GOCREATE TABLE [CRPDTA].[F55MRKT11](mdan8 int,mdac02 varchar(5),mdmot varchar(5),mdmail int,mdmag int,mdupmj int)What I would like to do is place a trigger on F55MRKT119 which willinsert records to the F55MRKT11 if they do not exist in that tablebased on the [mdan8] field. If the record does exist I would likeUpdate the corresponding record and increment either the [MDMAIL] orthe [MDMAG] based on the inserted [MHMOT]. What I have so far is asfollows:TRIGGER #1:CREATE TRIGGER trgIns_Summary ON [CRPDTA].[F55MRKT119]FOR INSERTASBEGININSERT INTO CRPDTA.F55MRKT11select INS.MHAN8, INS.MHAC02, INS.MHMOT,case when INS.MHMOT='MAG' then 0 ELSE 1 end,case when INS.MHMOT='MAG' then 1 ELSE 0 end,'0' from INSERTED INSWHERE ins.mhan8 not in(select mdan8 from crpdta.f55MRKT11)ENDTRIGGER #2:CREATE TRIGGER trgUpd_Summary ON [CRPDTA].[F55MRKT119]FOR UpdateASBEGINUPDATE CRPDTA.F55MRKT11SET MDMAIL= case when INS.MHMOT='MAG' then 0+MDMAILwhen INS.MHMOT<>'MAG' then 1+MDMAIL end,MDMAG= case when INS.MHMOT='MAG' then 1+MDMAGwhen INS.MHMOT<>'MAG' then 0+MDMAG endfrom INSERTED INS JOIN CRPDTA.F55MRKT11on(ins.mhan8=mdan8)ENDFor instance if I do the following insert:INSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','AL4','0')thenINSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','MAG','0')This is what I expect in both tables:[CRPDTA.F55MRKT119] (2 Records)MHAN8 MHAC02 MHMOT MHUPMJ------ ------ ----- ------212131 VK4 AL4 0212131 VK4 MAG 0[CRPDTA.F55MRKT11] (1 Record)MDAN8 MDAC02 MDMOT MDMAIL MDMAG MDUPMJ----- ------ ----- ------ ----- ------212131 VK4 AL4 1 1 0The insert part works fine in that it iserts in both tables with thecorrect values. However it seems as if the Update protion is failingfor some reason. WHat I have tried so far is setting the trigger orderfor the update to run first and vice-versa, but still no luck. Anyhelp would be appreciated.

View 1 Replies View Related

Insert Trigger To Update Table

Jul 23, 2005

Hi,Does anyone know of a simple way to do this? I want to create aninsert trigger for a table and if the record already exists based onsome criteria, I want to update the table with the values that arepassed in via the insert trigger without having to use all the 'set'statements for each field (so if we add fields in the future I won'thave to update the trigger). In other words, I want the trigger codeto look something like this:if exists (select * from TableA where Fld1 = inserted.Fld1) then//don't do insert, do an update instead (would i want to rollback here?and will I have access to the 'inserted' table still?)Update TableASet TableA.<all the fields> = Inserted.<all the fields>where Fld1 = inserted.Fld1end ifAny help or ideas would be appreciated.Thanks,Teresa

View 3 Replies View Related

INSERT UPDATE Trigger Question

Jul 20, 2005

How would I write a trigger that updates the values of a Descriptioncolumn to upper case for even IDs and to lower case for odd IDs? Ineed this trigger to fire for INSERT and UPDATE events.

View 2 Replies View Related

Update Column Based On 1st Letter

Feb 25, 2014

I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:

/images/imagename.jpg

Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:

/images/a/aimage.jpg
/images/b/bimage.jpg

I can automate the physical move of the images into the correct directory but I need SQL update query that will update each column based on the 1st letter of the image.

So:

/images/aimage.jpg
/images/bimage.jpg

Updated to:

/images/a/aimage.jpg
/images/b/bimage.jpg

View 4 Replies View Related

SP5 Problem For Insert Trigger Update Function

Mar 8, 1999

In recent testing, I have found that the trigger function UPDATE under SQL Server 6.5 SP5 behaves differently then SP4. For insert, the update function returns true on items that have not been explicitly inserted.

Is this a bug? Is this a new feature change? Where can I get more information on a bug list for SP5?

View 4 Replies View Related

T-SQL (SS2K8) :: Update / Insert Trigger With Condition

May 15, 2014

I am currently in the process of writing an INSERT/UPDATE trigger in SQL 2012, the scenario is, I have a table called Quote, what I want to happen is when a record gets added to the table with a field called Approved, populated with a value or a record gets Updated and the Approved field gets populated I want the trigger to execute a stored procedure, passing through a value from a field called Account within the Quote table as a variable. I know I can achieve the action using one trigger "AFTER INSERT, UPDATE" but I am struggling on forming the rest of the trigger.

View 1 Replies View Related

Insert And Update With Trigger Causing Lock

Feb 20, 2008

Hi,
I'm taking an Excel spreadsheet (that could have around 30k rows) and processing it in SSIS. I essentially have a flag in one of the spreadsheet cols that indicates whether the record is already in the database or not.

I'm splitting the data using a conditional split on this column and using a OLE DB Destination (Fast Load) to perform the inserts and a OLE DB Command to fire a stored procedure to perform any updates. Both the OLE DB Destination and the stored procedure are hitting the same table and the two operations could be executing at the same time as they both appear directly after the Conditional Split, so the OLE DB Destination is set NOT to lock the table.

This seemed to work OK until recently. I've just added 2 triggers onto the table in question which I don't want to fire 30,000 times during the import. As the OLE DB Destination is set to use Fast Load, it doesn't fire the triggers - cool. In the update stored procedure it disables the trigger before performing it's update and re-enables the trigger when finished. Currently this does mean that if you only had updates, the trigger could be enables/disabled 30,000 times. That sounds kinda bad, but I don't really know if this carries a large overhead or not?

If, when importing now you have both updates and inserts the whole process locks up. From looking at activity monitor, it seems as though the INSERT gets suspended.

Do I have a fundamental problem with how I've structured the Data Flow or am I just being really stupid in Enabling/Disabling a trigger that many times, which is probably causing the problem?

Thanks in advance.

View 3 Replies View Related

Issue During Update And Insert (trigger Problems)

Sep 24, 2007

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

View 3 Replies View Related

Insert Or Update Null If The Value Is Zero Using Trigger On Table

May 16, 2006

Hi ,

I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)

To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp

Thanks in advance

regards,

Srinivas Govada

View 6 Replies View Related

Trigger After Column Update

Oct 22, 2007

I have a table called Projects, within that table is a field called UserId

I want to log everytime that UserID field in changed in a log called ProjectAllocationLog

so far i have written

CREATE TRIGGER LogUser
ON Projects
FOR Insert, Update
AS
if Update(UserId)
begin

INSERT INTO "ProjectAllocationLog" ("projectid", "UserID", "createdate")
VALUES ("123", "123", Now())

End

What I am stuck with is how do I pass the values of the field into the Values

ie where I have put "123", I actually want to pass the value of ProjectID, and UserID from the projects row that is changing

can anyone help me please

View 5 Replies View Related

Update Column Value Based On Value In Another Column?

Jan 29, 2008

Hi.

I'm writing a web application with VS2005 andSQL Server 2005 express edition.

I have an SQL table:

Table name:
statistics

Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage

When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:

UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)

This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)

Any help would be appreciated.

View 2 Replies View Related

Automatically Trigger A Sum From One Table To Another Upon Update/insert Query

Jul 12, 2005

I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID

I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons

I know this is possible in access, so i'm wondering how to go about it in ms-sql?

any ideas?

View 1 Replies View Related

Create Trigger To Check Values At Insert/update

Feb 10, 2004

I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:

---------------------------------
CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client]
FOR INSERT, UPDATE
AS
declare @currency as char(50)
declare @country as char(50)

declare cur cursor for SELECT currency, country
FROMinserted

OPEN cur
fetch cur into @currency, @country
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select * from listinfoid where listname = 'currency' and listid = @currency)
begin
set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency')
CLOSE cur
DEALLOCATE cur
RAISERROR (@currency,16,-1) with log
return
end
if not exists(select * from listinfoid where listname = 'country' and listid = @country)
begin
set @country = (cast(@country as varchar (3)) + ' is not a valid contry')
CLOSE cur
DEALLOCATE cur
RAISERROR (@country,16,-1) with log
return
end
else
fetch cur into @currency, @country

END
CLOSE cur
DEALLOCATE cur
update Client set currency = UPPER(currency), country = UPPER(country)
---------------------------------

I use a cursor to handle multiple rows in an update query.
(SQL2000-server)

Is there an easier och better way to do this?
I�m a bit unsure of this code.

Thanx!

/Erik

View 2 Replies View Related

T-SQL (SS2K8) :: After Update Trigger - Only Insert Records Not Exists

Jul 30, 2014

I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:

ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
FOR UPDATE
AS
DECLARE @rc AS INT ;

[code]....

View 2 Replies View Related

How Create Trigger Stop Update Delete And Insert

Apr 11, 2008

How to create trigger to stop the delete , updation and insert in the table of database ....

How can i stopped .......................I want to apply on whole table of database

Pls help me out.

Yaman

View 3 Replies View Related