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 RepliesHelloI 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
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
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].....
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 RelatedIn 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
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
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 RelatedHi,
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
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
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
I have a dilema, I need to have a delete trigger enabled to track user deletes to update an external history table. However, when the posting process runs for the table for which the delete trigger runs, all the records from that table are deleted. Th end result is that instead of capturing the specific deletes, it shows all recods being deleted. I know you can disable foreign keys and triggers as a whole, can you do it for specific triggers?
View 1 Replies View RelatedHi!I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:periode startdate enddate1 23.08.2006, 15:00 24.08.2006, 17:002 24.08 .2006, 17:00 25.08.2006, 08:00and so onCan anyone help me how to do this? Thanks!
View 5 Replies View RelatedDo triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
How to set ddl triggers for specific tables, instead of on entire database.
View 2 Replies View RelatedAre 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 RelatedI want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test](
[Customer] [varchar](12) NULL,
[Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values
)
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
Hi, I'm a newbie doing my very first UPDATE procedure. I want to UPDATE a specific row in my Sql db table, WHERE FirstName = "John" AND LastName = "Smith". What am I missing in my code below to do this specific update: Most greatful for any help, thanks.<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
UpdateCommand="UPDATE [CompanyDepartment] SET [User_Name] = @User_Name, [FirstName] = @FirstName, [LastName] = @LastName, [Company_Name] = @Company_Name, [Department_Name] = @Department_Name WHERE [User_ID] = @User_ID ">
<updateparameters>
<asp:parameter Name="User_Name" Type="String" />
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:Controlparameter Name="Company_Name" Type="String" ControlID ="ListBox1" PropertyName ="SelectedValue" />
<asp:Controlparameter Name="Department_Name" Type="String" ControlID ="ListBox2" PropertyName ="SelectedValue"/>
<asp:parameter Name="User_ID" Type="Int32" />
</updateparameters>
</asp:SqlDataSource>
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
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?
Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
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 ?
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
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
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.
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
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
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?
Trying to create a trigger on a table that will delete any records in it that do not exist in another table.
Inserting into OrderRebateHistory Table.
if ordtype, ord_no, and line_seq_no do not exist in oelinhst then delete from OrderRebateHistory or do not insert.
Right now my code will insert the record. Then when another record gets inserted the previous record is deleted.
CREATE TRIGGER [DeletefromOrderRebateHistory] ON [dbo].[OrderRebateHistory]
for INSERT
AS
DELETE OrderRebateHistory
FROM OrderRebateHistory inner join oelinhst_sql on oelinhst_sql.ord_type = OrderRebateHistory.ord_type and
oelinhst_sql.ord_no = OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no = OrderRebateHistory.line_seq_no
where oelinhst_sql.ord_type <>OrderRebateHistory.ord_type and oelinhst_sql.ord_no <> OrderRebateHistory.ord_no
and oelinhst_sql.line_seq_no <> OrderRebateHistory.line_seq_no
hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in another tabletblA records are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh
View 1 Replies View Relatedhello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in table tblArecords are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC. This view vwC is based on a key field used in tblA.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh
View 8 Replies View RelatedHi how can i use delete trigger?
For example i've a two table like emp and emp_personal and now what i want to do is
i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ?
Ex:
emp emp_personal
emp_id emp_name emp_basic emp_id emp_address
101 Nagu 32,000 101 India
102 Vijay 35,000 102 South Africa
103 Ritesh 30,000 103 U.S
I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ?
Is it possible with triggers?
Thanx - Nagu