Help ! How Do I Refer To The Deleted Table Into A Trigger Statement ?
Jul 20, 2005
Hi there,
Could somebody post some simple example how one can refer to
a column in the 'deleted' temporary table within a trigger definition ?
Should the 'deleted' and 'inserted' temp tables be declared, or they are
'implied' ?
I keep having this msg when trying to create a trigger
"The column prefix 'deleted' does not match with a table name or alias name
used in the query"
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
For some reason in Enterprise Manager for SQL Server 2000, I cannotput the following line into a trigger:select * into #deleted from deletedWhen I hit the Apply button I get the following error:Cannot use text, ntext, or image columns in the 'inserted' or'deleted' tablesThis seems like a weird error, since I am not actually doing anythingto the inserted or deleted tables, I am just trying to make a tempcopy.I have another workaround but I am just curious why this happens.Thanks,Rebecca
I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:
CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as update cp_shiptos set date_updated = GETDATE()
Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated.... for example:
CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as update cp_shiptos set date_updated = GETDATE() where shipto_id = @THIS_ID
I imagine there's some kind of builtin variable or something like that. How is this done?
I need the value stored in tblTwo.GuideMult (ie. One, BaseRate) to be translated into the numerical value shown on a webform (ie. @One, @BaseRate) and then insert the numerical values into tblOne.GuideMult
Clear as mud? Does somebody have a better way to do this?
please let me know how to refer to a row from a set of duplicated rows of a sql server database table.there are no primary keys/constraints in the table so as to refer it.Is there any thing like 'rowid' which we have in oracle?
I posted a thread in the Getting Started forum about how to make a counter for maximum tickets : http://forums.asp.net/t/1215258.aspx but maybe this is a more appropriate forum for this subject. In a school project, we are making a website for a fictional concert/festival (using Visual Studio 2005, C#). On that site users can register and order tickets. We have access to an SQL-database, by the way, where we can create tables etc. We want the maximum amount of tickets to be 10000 per day. The festival is supposed to last from friday to sunday. What would be the best way to do this programatically? The counter should maybe be in an own database table?
Is there an alternative to using FETCH to loop through the Inserted/Delete Tables within a trigger? Does this work?
SELECT * FROM Inserted BEGIN
if INSERTED.IsActive then ...
END
Would this only see the first record?
Currently I'm doing the following;
AS DECLARE @JobID INTEGER; DECLARE @IsActive BIT; DECLARE Temp CURSOR FOR SELECT JobID, IsActive FROM Inserted; BEGIN OPEN Temp; FETCH NEXT FROM Temp INTO @JobID, @IsActive; WHILE (@@FETCH_STATUS = 0) BEGIN
if @IsActive then ...
FETCH NEXT FROM Temp INTO @JobID, @IsActive; END; CLOSE Temp; DEALLOCATE Temp;
Is this the best method for looping through the Deleted/Inserted or any other table within a trigger?
I would like to know how can I write a trigger for a table that lists folders deleted. I have a table called RECORDS which contains folders and would like to know folders that are being deleted.
I need to add the row number or record number to the 'inserted' and 'deleted' tables in a trigger.
Among other things, I have tried-
SELECT 1 as rowId, i.* INTO #ins FROM inserted i if @@ROWCOUNT > 1 --if multiple rows, set row number begin
SELECT @pkWhere = coalesce(@pkWhere + ' and ', ' where ') + PKF.colName + ' <= i.' + PKF.colName FROM #primaryKeyFields PKF set @strSQL = 'update #ins set rowId = (Select Count(*) From #ins i' + @pkWhere + ')'
exec (@strSql)
end
-the above sets rowId for every record to the total instead of sequential. Keep in mind that this code is used to create a trigger for any table, hence I cannot specify any column names literally.
I created manage update trigger to react on one column changes. There is an application which is working with DB, so I don't have access to SQL query which changes this column. In most cases trigger works fine, but in one case when this column changes, trigger is fired and IsUpdatedColumn is true for this column, but both inserted and deleted table are empty, so I can't get new value for this column. Any idea why is it happened? Is any way around?
This column type is uniqueidentifier. Inserted and deleted tables are empty when application is changing value from NULL to not null value, but if I change it myself from Management Studio inserted table contains right values. Most like problem is in query which is changing that value.
I dont know what I am doing wrong. The trigger (see below) is doing what I want it to do when I do this:
INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' , 'Boss')
but when I remove one value, the result in the logtable is NULL:
INSERT INTO dbo.personal (personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' )
How can I change the trigger so that it will give me the information of the values that have been updated, inserted or deleted when I dontchange all values (just a couple of them)?
My trigger: CREATE Trigger trigex
ON dbo.personal
FOR insert, update,delete
AS
INSERT INTO logtable (Innan_värde, Ny_värde)
SELECT
rtrim(cast(d.personal_id as varchar)+', '+cast(d.chef_id as varchar)+', '+rtrim(d.efternamn)+', '+ rtrim(d.fornamn)+ ', '+ rtrim(d.titel)),
(cast(i.personal_id as varchar)+', '+cast(i.chef_id as varchar)+', '+rtrim(i.efternamn)+', '+ rtrim(i.fornamn)+ ' '+ rtrim(i.titel))
FROM inserted i full join deleted d on i.personal_id = d.personal_id
We have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do this?
Is there a way to check who has deleted records from my SQL tables? I asked because I notice records keep disappearing from my tables recently for no reason. The DB is only accessible by a few IT staff. Business users have no direct access to it so they can't do any harm and there is no application that update these record missing tables.
I don't need to know the exact records that have been removed. I need info on who has made a deletion activity on which table, date and time is good enough.
Hi all I would like to know if its possible to "Save" records when they get deleted. For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc... In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view. All this should go to a seperate database or seperate table, it doesnt really matter. My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
It happened accidently that we deleted records from a table in SQL server 2005 DB. We never took a full backup of the DB till then. Is there any way that we can recover the deleted records. Logs files are still present. ( *mdf and *ldf ).
Is there way to undelete deleted records from a table? - For security reasons I need to delete records without removing them out of the database.
In other words: target is to design a procedure to delete records only logical.
I had the idea to use a field, saying record is deleted or not. This field would control if data is hidden (logical deleted) or not. To undelete a record, only this field would change its state.
But it becomes difficult if you have many FK to logical deleted data.
I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.
Can any one tell me ..is there any restriction on the total number of records that can be added to a table ( SQL Server 2005) .Because .. i added 100 records to a table through my program..but i was able to c ..only the last 20 records which is being inserted..can any one tellme any way to increase the total number of records..
pls..help me..solution is required immediately.........
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
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'
