Please Help Create Trigger Condition On Update
Jun 24, 2008
please help create trigger condition on update
IF EXISTS
(
SELECT *
FROM empList
WHERE (unit = 9)
)
begin
update [dbo].[empList]
set unit = CASE WHEN SELECT na,empID, unit
FROM empList
WHERE (empID IN (111, 222, 333, 555)) AND (unit = 9))
then '4' else t.fld1 end
i have an emmployee table
empid unit
------------------------------------------------------
1111 3
2222 9
3333 9
4444 2
5555 2
6666 1
7777 9
8888 2
9999 9
-----------------------------
i need help create trigger condition on update like this
ONLY ON
EMPID=2222,3333,7777,9999
WHAN ON UPDATE they have unit =9 THAN
I NEED THE trigger DO THIS
empid unit
------------------------------------------------------
1111 3
2222 4
3333 4
4444 2
5555 2
6666 1
7777 4
8888 2
9999 4
ONLY IF someone update EMPID=2222 OR 3333 OR 7777 OR 9999 and UNIT=9
THAN automatic change 9 TO 4
TNX for the help
View 1 Replies
ADVERTISEMENT
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
May 8, 2008
Hi,
I have a table with somefields, here i will only mention on which i need to perform an action, possibly with the use of Trigger.
Fields = Active, inactiveDate
Active Field is of bit datatype mean conatins 1 or 0,
1 means the user is active, but when i change the active field to 0, and make the user inactive i want the date to be populated automatically to inactiveDate field when active field is changed to 0.
any help is much appreciated
NAUMAN
View 2 Replies
View Related
Aug 17, 2007
Hi,
I have the following situation.
I have one table named as "Order", and other table as "Shipment". Now, I want to check if one column name "Status" in the "Ă–rder" table has been changed (when it becomes status = 7) then I need to insert records in "Shipment" table.
Order Table:
OID===Order_Product===Order_Description===Order_Status
Shipment Table:
SID===Shipment_Description===DateTime===Status
I tried to create a trigger, but it is not working properly;
Create Trigger trg_InsertShipment ON [dbo].[Order]
FOR Update
AS
INSERT INTO Shipment
(
SID,
Shipment_Description,
DateTime,
Status
)
Select
'001' as SID,
'Nothing' as Shipment_Description,
'01/01/2007' as DateTime,
'Ready' as Status
WHERE Order.Status = 7
===============================================================
But it inserts the records into Shipment Table every time the status field or any other field in the Order Table is changed. What I want is that, "When Status Column in Order Table is Updated to 7, then insert record in Shipment Table".
How can I do that???
Thanks in advance...
View 5 Replies
View Related
Aug 2, 2007
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
View 3 Replies
View Related
Feb 5, 2005
Hi,
I'm using DataAdapter.Update() to update data in a table. My question is; how do I create a trigger that works after the update has completely finished?
For example if my update adds 50 new rows to a table the trigger I've currently got fires after each new row that is added ie 50 times in total. Is it possible to get it to trigger after the last (ie 50th) row is added???
Thanks
View 2 Replies
View Related
Apr 30, 2007
hi,CAn i have one trigger for both Update and DeleteDelete Trigger---------------------create Trigger [tr_delete_user_log]on [dbo].[user_log] for deleteasbegininsert into z_user_log select * from deletedendTrigger Update---------------------CREATE Trigger [tr_update_user_log]on [dbo].[user_log] for updateasbegininsert into z_user_log select * from deletedendCan i have one trigger instead of these Triggers ..
View 3 Replies
View Related
Jun 4, 2007
Hi
Apologies if this is a silly question
I have a basic table "Customer" which has
Id
Address1
Address2
Address3
Town
County
Postcode
SearchData
After I insert or Update a row in this table I need to update the SearchData column
with
UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)
only for that Id
I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.
Regards
David
View 4 Replies
View Related
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
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
Jan 11, 2008
Hi everyoneI am trying to create a DELETE Trigger. I have 2 tables. Table1 andTable2. Table 2 has all the same fields and records as Table1 + 1extra column "date_removed"I would like that when a record is deleted from Table 1, the triggerfinds that record in Table2 and updates the date_removed filed withcurrent time stamp.The primary key on both is combination of domain,admin_group and cn.CREATE TRIGGER [tr_updateTable2] ON [dbo].tbl_Table1FOR DELETEASUpdate Table2SET date_removed = getDate()I'm stuck here, how do I manipulate on Table2 only the records thatwere deleted on Table1, so to only update date_removed filed for themin Table2?I guess i need to compare domain, cn and admin_group, but I don't knowhow.Any help would be greatly appreciatedThanks! :-)
View 3 Replies
View Related
Jan 24, 2008
how to do this
i have table of employee ,evry employee have a unique ID "empid"
empid VAL_OK
--------------------------
111 0
222 0
333 0
now insert multiple insert to my work_table shifts for all month for evry employee
like this
(this is work_table)
empid date val
--------------------------------------------------
111 01/02/2008 1
111 02/02/2008 2
...............
111 29/02/2008 5
--next employee
222 01/02/2008 1
222 02/02/2008 4
...............
222 29/02/2008 6
--next employee
333
--next employee
444
--next employee
555
-------------------------------------------------------------
now i need for evry OK insert (for all month) each employee
go to the TB_Employee
and update each employee once !!
from VAL_OK=0 to VAL_OK=1
like this
empid VAL_OK
--------------------------
111 1
222 1
333 1
----------------------
like this i know who is the employee have shift for all month and who NOT !
i think it like this
Code Snippet
Create trigger for_insert on tb_work
For insert
begin
if @@rowcount = 1
Update tb_employee
Set
val_ok= 1
else
/* when @@rowcount is greater than 1,
use a group by clause */
Update tb_employee
set
val_ok= 1
select empid from tb_work
group by tb_work.empid
End
TNX
View 4 Replies
View Related
Nov 8, 2005
One of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
View 3 Replies
View Related
Apr 11, 2007
I simply want an email to go out if any records in a table exist, else no email. I have other steps completed in this DTS job but this is the last step. Any ideas?
ddave
View 1 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Jul 7, 2007
How can update / insert records from table1 colum to table2 colum
with condition ?
I want to insert/update records like this.
update employee
set joine_date = select joine_date from master_table
where emp_id.employee=emp_id.master_table
regards
Martin
View 2 Replies
View Related
Mar 2, 2004
can anyone help to create storeprocedure in SQL SERVER 2000?
the case is like this:
i have one table call [TIMETABLE] which contains these fields:ID,start_time,finish_time,subject_code,venu e
and another table call [attendant_sheet] has these following fields:ID(which should be from TIMETABLE),student_id,subject_code,status,date.
and the storeprocedure will works like this:
if the time(system time) has pass the finish_time on TIMETABLE table then insert the related field into attendant_sheet. and then update the status field to 'absent'
and the student_id should be get it from another table call [enrolment] who havent take their attendance and enrols related subect?
could this possible? PLZ HELP ME!
View 4 Replies
View Related
Aug 10, 2014
Table A - data example
Idno Entity Flag
1 1 y
2 1 n
3 1 y
4 1 y
Table B - data example
Idno Entity
1 1
2 1
3 1
Table C - data example
Idno Entity
4 1
I want to update the value flag in table A.Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y. 4th record where idno A not in table b and in Table C so flag will change from y to n. is it possible, or do i need to use the "fetch" method to compare record by record.
View 4 Replies
View Related
Sep 20, 2007
Hi
I have a two tables as follows
Table Category
{
ID PK,
LastUpdate DateTime
}
Table Master
{
ID PK
Catrgory DateTime
}
I wanted to update Catrgory coulmn of all records in the Master table with the Value of LastUpdate of the CategoryTable the where the ID of the both the table are same
Can any one please let me know the query
~Mohan
View 8 Replies
View Related
Jun 30, 2006
Hi all,
HERE IS MY UPDATE STATEMENT:
Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM
WACTS_Inventory_Part_Loc_Tbl WIPLT
INNER JOIN
Temp_MatIn_Data tmp
ON
WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID
WHERE
WIPLT.WIPLT_Location_Code='MF'
I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.
The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.
Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.
I woould appreciate any help on this.
Thanks,
Jothi
View 4 Replies
View Related
Jan 22, 2008
hi, I want to ask how I want update my table using a multiple condition.My table format is like this:
IC_NO
F05
F07
F08
F09
Ind
123
452
C
654
852
P
125
A
526
C
What I want to do is:
IF Ind = C, F05 <> NULL then IC_NO = F05
IF IND = C, F05 = Null and F09 <> Null then IC_NO = F09
IF IND = P, F05 <> Null and F07 <> Null then IC_NO = F07
IF Ind = A, F05 = Null and F08 <> Null then IC_NO = F08
your helpful highly appreciated..thanks
View 10 Replies
View Related
May 7, 2008
Hi to all:
I'm trying to make a sp who generates an export string for everey sql command, for example: Insert, update and delete. In the case of Insert I just have the sp, but in "update" and "delete" commands... how can I know the conditions?..
UDTATE DemoTable SET DemoField=77 WHERE (Condition1 AND Condition2)
....may I use a comparative with 'updated' table?, please help me with your ideas
Thanks a lot.
View 4 Replies
View Related
Feb 16, 2005
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr
From Results, PledgeInLastYear
Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear
Insert Into Results(PledgeLastYr)
Values ('Y')
View 1 Replies
View Related
Jul 21, 2015
I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name  in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .
View 5 Replies
View Related
Mar 18, 2008
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052 Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
how to achieve this in single query?
View 4 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Â
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
May 30, 2008
Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
View 3 Replies
View Related
Jul 20, 2005
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray
View 1 Replies
View Related
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
May 10, 2006
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
sqlDataSource1.Update();
sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang
View 2 Replies
View Related