Create One Trigger For Both Update And Delete
Apr 30, 2007
hi,
CAn i have one trigger for both Update and Delete
Delete Trigger
---------------------
create Trigger [tr_delete_user_log]
on [dbo].[user_log] for delete
as
begin
insert into z_user_log select * from deleted
end
Trigger Update
---------------------
CREATE Trigger [tr_update_user_log]
on [dbo].[user_log] for update
as
begin
insert into z_user_log select * from deleted
end
Can i have one trigger instead of these Triggers ..
View 3 Replies
ADVERTISEMENT
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
Jun 14, 2007
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
View 1 Replies
View Related
Sep 28, 2006
HelloI have a Trigger on a table. Here is the code ALTER TRIGGER [dbo].[OnOrderDelete]
ON [dbo].[orders]
AFTER DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ids int;
SELECT @ids =( SELECT id from DELETED);
DELETE FROM files WHERE OrderId = @ids;
ENDActually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:When orders table is updated, the "SELECT @ids =( SELECT id from DELETED);
DELETE FROM files WHERE OrderId = @ids;" part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @ids =( SELECT id from UPDATED) ") Is this right? And how can I part UPDATED and DELETED ?ThanksArtashes
View 12 Replies
View Related
May 2, 2007
I have a table where I want to prevent user from deleting or setting a flag on a field to "y" with a database trigger (sql 2000). I understand the trigger for just one (stopping the delete, or stopping the field being changed to "y"). Should I have 2 seperate triggers or would there be a way to handle both.
View 4 Replies
View Related
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
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
Aug 9, 2006
In SQL Server 2005 Express I created a trigger which should edit a value in another table when a special tupel column has been updated. The funny thing is, that it also fires on delete...of course not what I wanted. what did I do wrong?
Thanks in advance!
ALTER TRIGGER dbo.update_history_users
ON dbo.[user]
AFTER UPDATE
AS
IF UPDATE([name])
BEGIN
DECLARE @old_value nvarchar(MAX),
@new_value nvarchar(MAX);
SELECT @old_value = name FROM DELETED ;
SELECT @new_value = name FROM INSERTED ;
UPDATE dbo.history SET user_name = @new_value WHERE user_name = @old_value;
END
View 5 Replies
View Related
Feb 1, 2007
Hi,
I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.
My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.
Are there any other idea's how can i solve this?
Best regards,
Thomas
View 2 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
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
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
Mar 22, 2007
Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete. I right click on the adapter bar and select Add Query. I sleect 'Use SQL Statements'I select 'Update' (or 'Delete')I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious. Diane
View 5 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
May 18, 2006
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
View 4 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
Mar 12, 2007
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies
View Related
Nov 13, 2006
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
View 6 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
Mar 18, 2008
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 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
Jun 14, 2002
Hi,
In Oracle, we have FOR EACH ROW Concept in triggers which can be used to manipulate to fire the trigger for each row.
How can we implement the same in SQL Server 7.0 ?
View 1 Replies
View Related
Apr 23, 2001
Hello,
For SQL Server 6.5.
I need help if there is a way to fire a delete trigger when accidentenlty somebody deletes a record from say table AAA then this record should be
inserted in another dump table say Table BBBDump with table schema as table AAA plus some other columns like DB_NAME,Time when recorded
deleted from table AAA, user ID .
Thanks
Rajiv
View 1 Replies
View Related
Nov 5, 2001
hi, I have a table as follow:
id amount
1 100
1 100
1 200
1 300
I put a deleted trigger on this table when a record is deleted, I update a balance in another table based on the deleted amount. This works fine when I am deleting one record at a time, but when I delete more than one record, the first value from the first record is captured to update the balance due and the rest of the of the records has not effect.
To elaborat more,
delete * from table1 where id =1 and amount =200 -- this works fine
delete * from table1 where id =1 and amount =100 -- the first 100 only update the balance in another table the second 100 does nothing,
any ideas, I appreciate your help.
Thanks
Ali
View 3 Replies
View Related
Dec 13, 2004
I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table.
The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message.
For each row deleted I need to update a column in another table (PACCESOS_CAB).
This the trigger...
CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET
FOR DELETE
AS
declare @mdias as int
declare @mFKFeria as int
declare @mtipo as char(1)
declare @mfkcontacto as varchar(7)
if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo)
begin
select @mfkferia=m.fkferia, @mfkcontacto = m.fkcontacto, @mtipo = m.tipo, @mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo
update PACCESOS_CAB set diasvisita = @mdias -1 where FKFeria= @mFKFeria and FKContacto=@mFKContacto and Tipo=@mTipo
end
Thanks in advanced.
View 3 Replies
View Related
Apr 28, 2008
i have created a view from two tables in my database (ProjectedOutputs, and Output)
since I am using the view in vb to fill a grid, i am trying to set up a INSTEAD OF DELETE trigger to delete any records in ProjectedOutputs that may be deleted from my grid. Although the records are deleting OK, so are the records in Output, which i dont want. i am not sure how to stop this, or to see if my trigger is actually firing, I would appreiciate any suggestions
CREATE TRIGGER Test
ON V_ProjectOutputs
INSTEAD OF DELETE
AS
DELETE PROJECTEDOUTPUTS
FROM PROJECTEDOUTPUTS JOIN deleted ON PROJECTEDOUTPUTS.ProjectedOutputID = deleted.ProjectedOutputID
View 12 Replies
View Related
Feb 27, 2004
Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10:
"Cannot use SAVE TRANSACTION within a distributed transaction."
Here follows the code. Hopefully anybody can help me with this problem.
SQL-statement:
Function getDeleteRequestSQL(ByRef strRequestId As String) As String
Dim strSQL As String
strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
getDeleteRequestSQL = strSQL
End Function
And then the place where the error occurs.
GetConnection cnConn
strSQL = getDeleteRequestSQL(reqId)
10 cnConn.Execute strSQL, , adExecuteNoRecords
And finaly the trigger:
create trigger td_thmld2 on thmld2 for delete as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
select @numrows = (select count(*) from trigstat with (nolock) )
if @numrows > 0
return
save transaction trans_td_thmld2 <-- REASON FOR ERROR?
/* delete all children in "thmstat" */
delete thmstat
from thmstat t2, deleted t1
where t2.hdmcode = t1.hdmcode
/* delete all children in "thmldlk1" */
delete thmldlk1
from thmldlk1 t2, deleted t1
where t2.hdmlmldcode = t1.hdmcode
/* errors handling */
error:
raiserror @errno @errmsg
rollback transaction trans_td_thmld2
end
View 2 Replies
View Related
Apr 16, 2008
I'm trying to capture 'what' is deleting records from my tables.
I know what the data is but how do I know which user did it or which stored procedure caused it?
View 1 Replies
View Related
