Database

kubben24-Sep-07 8:07

24-Sep-07 8:07

You must use the inserted table for your insert so your code should be something like:
Forum SQL / ADO / ADO.NET
Subject: Re: problem with create trigger
Sender: md_nada
Date: 8:28 24 Sep '07

i try to create trigger on table to insert values in another table but
the is syntax error
(Msg 1046, Level 15, State 1, Procedure MoveStudentInfo, Line 20
Subqueries are not allowed in this context. Only scalar expressions are allowed.)

here is the trigger script

create trigger MoveStudentInfo on Temp_Code

after insert

as

insert into Student_Info (Stu_Code,Stu_Name,Sex,Nationality,Relagion,Address
,ZipCode,Phone,Mobile,Email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,School_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang)

select i.Stu_Code, Stu_Name,Sex,Nationality,Relagion
,Address,ZipCode,PhoneNO,Mobile,email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,school_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang from inserted i
join Student_Registry sr on sr.Student_ID= i.Stu_ID

Ben

Mohammed Elkholy24-Sep-07 9:29

Mohammed Elkholy

24-Sep-07 9:29

i dont get the data from the table i add the trigger for him

i get the data from another table

so i cant use inserted

MD_NADA

kubben24-Sep-07 10:49

24-Sep-07 10:49

Did you even look at the code I posted? I use inserted to join to the table that has the data you need. Your code used inserted to get the value and then use that variable to select the data. Take a look at the code I posted again.

Ben

Difference between Delete and Truncate?

joemonvarghese24-Sep-07 20:31

joemonvarghese

24-Sep-07 20:31

hi

please change the insert statement as follows....

insert into Student_Info (Stu_Code,Stu_Name,Sex,Nationality,Relagion,Address
,ZipCode,Phone,Mobile,Email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,School_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang)

select @Stu_Code,Stu_Name,Sex,Nationality,Relagion
,Address,ZipCode,PhoneNO,Mobile,email,District,BirthPlace,BirthDate,Certificate_Type
,Certification,Department,Grad_Date,Total,Grade_Total,school_name,Stu_Image
,School_SeatNO,National_ID,Region,First_Choice,Second_Choice,Third_Choice
,First_Lang,Second_Lang from Student_Registry where Student_ID=@Stu_ID

ganeshMohan23-Sep-07 20:43

ganeshMohan

23-Sep-07 20:43

hai

if anyone known the difference between the delete and truncate

and please give me an example

thanks in advance

Ganesh Mohan

John-ph23-Sep-07 21:18

John-ph

23-Sep-07 21:18

Delete and Truncate performs Identical tasks. but there are differences...

Delete is a DML command used to delete a particular record or some set of Records based on the WHERE-Clause. And It can also Delete all the Records when there is no WHERE-Clause specified. The Delete operation performed using Delete Command can be Rolled Back.

Truncate is a DDL Command used to delete all the Reocrds from the Table and also it Reinitializes the Table. Primary Key Identity values will be regenerated from the initial value for the subsequent entries after deleting all reocrds. WHERE-Clause can't be used in Truncate Command. The Delete operation performed using Truncate Command can't be Rolled Back.

Truncate is much faster than Delete

Note : Truncate = Delete + Commit

Regards
J O H N

"Even eagles need a push." David McNally

Pete O'Hanlon23-Sep-07 22:30

Pete O'Hanlon

23-Sep-07 22:30

Good answer. Get's a 5 for its clarity and completeness.

Deja View - the feeling that you've seen this post before.

John-ph23-Sep-07 23:11

John-ph

23-Sep-07 23:11

Thanks Pete.

Regards
J O H N

"Even eagles need a push." David McNally

Pete O'Hanlon23-Sep-07 23:29

Pete O'Hanlon

23-Sep-07 23:29

You're welcome. It's always a pleasure to read a detailed and accurate answer.

Deja View - the feeling that you've seen this post before.

kubben24-Sep-07 8:32

24-Sep-07 8:32

I think one other difference is that Delete is logged and truncate is not. Thus the ability to roll back etc. That is also part of the reason that truncate is faster.

Ben

kubben24-Sep-07 8:35

24-Sep-07 8:35

Ok so I guess part of it is logged. I had to look it up.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

Ben

ganeshMohan24-Sep-07 19:19

ganeshMohan

24-Sep-07 19:19

Thanks John

Mike Dimmick24-Sep-07 4:09

Mike Dimmick

24-Sep-07 4:09

One other difference is that the user doing the TRUNCATE must have greater permissions than doing a DELETE. DELETE permissions can be granted to a user or role on a per-table basis; on SQL Server 2000, TRUNCATE permissions are only granted to the owner of the table, members of the sysadmin server role, and members of the db_owner or db_ddladmin fixed server roles.

SQL Server 2005's documentation is a little unclear, it says that the user must have ALTER permissions on the table but then repeats the sentence from SQL Server 2000 Books Online.

Obviously if you're connecting to SQL Server using sa or a trusted connection (Windows authentication) using an administrator account, you have those permissions. However, this is not a good practice as those accounts have full control over the entire database server, and as such, a mistake in your application logic could lead to accidental, or even deliberate, damage. In my view, you should firewall what an application can do by setting up less-privileged server logins and database users.

DoEvents: Generating unexpected recursion since 1991