473,246 Members | 1,534 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Help ! How do i refer to the deleted table into a trigger statement ?

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"

Thanks for your inputs,
Eddy.

Jul 20 '05 #1
2 9861
[posted and mailed, please reply in news]

Eddy Fontaine (ed***********@advalvas.be) writes:
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' ?
They are implied. You just use them.
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"


May I guess that you are using dynamic SQL? Code executed in EXEC() or
sp_executesql is not part of the trigger, but constitutes a scope on
its own.

A practical workaround is to say:

SELECT * INTO #deleted FROM deleted
SELECT * INTO #inserted FROM inserted

first in the trigger.

This can even be useful in a trigger that does not use dynamic SQL,
particular if multi-row operations are common, because you can get
better performance. The virtual tables inserted and deleted are
not very efficient when they get some size.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Eddy Fontaine (ed***********@advalvas.be) writes:
Thanks Erland,
I was trying to refer to a column in the deleted temp table with
'deleted.column_name'...
My sql server does not like this !
Since you never posted the code to the trigger that gave you the
error message, I cannot say what your problem really was. Judging from
the code you post now, may you tried with:
UPDATE DemographicData SET Lastupdate = getdate()
WHERE ID = deleted.ID


which is not legal, but that has nothing to do with inserted/deleted,
but with the fact that you must name a table in a query in a FROM
clause or after INSERT/DELETE/UPDATE.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Leonardo Almeida | last post by:
Hi everybody, How can I Update a field from another table by Trigger? Can someone send me the statment to do it? I have a table called Clients with fields : ID_Clients, Client And Another...
2
by: Helmut Blass | last post by:
hi folks, in the course of my Access project (ACC2000) I created a temporary table which I deleted after it was no longer needed. now, each time I start Access, I am getting annoyed with the...
9
by: baonks | last post by:
hello all here is my problem: I have 2 table 1: K_POS SALDO_A_D SALDO_A_K 11100 105 5 11200 5 105
5
by: joebin | last post by:
I accidentally deleted the switchboard item in form list. Anyone know where I can get my switchboard back? The Switchboard Items in table list is still there. I can also view all the switchboard...
5
by: abctech | last post by:
Hi all My frontend is Java and backend is MySql. The tables that I create have auto incrementing primary keys. This is the syntax of my create statement :- Create table ABC(ID Int not...
14
by: DavidOwens | last post by:
Hi there everybody iv designed a database, curently when the delete button is pushed, it deletes the record completly, but i dont want that i want it to just disable the dispenser. iv crreated,...
3
by: jdprime | last post by:
Hi, I am trying to write a trigger that will run when someone updates the delivery charge within the specified table. It will work, but will only record one change to the table. My question is, if...
4
by: rcamarda | last post by:
I have a UDF that cleans a field of control characters and I use it like this select dbo.udf_CleanAlphaNum(Address1) as Address1 from Leads It works great. I use it to clean several fields...
6
by: MLH | last post by:
Utilities to recover tbl removed from TABLE tab of database window with the DELETE key?
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.