By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

Trigger

P: 95
create table employee(id int,name varchar,address varchar(10))
insert into employee values(1,'A','FBD')
select * from employee

create table user1(id int,user1 varchar,password varchar(10))
select * from user1

CREATE TRIGGER trigger1
On Employee
FOR INSERT AS
BEGIN
INSERT user1(id,user1,password)
SELECT id,name,name+123 FROM INSERTED
END

This trigger is not working correctly errors are there plz reply
Feb 12 '08 #1
Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
create table employee(id int,name varchar,address varchar(10))
insert into employee values(1,'A','FBD')
select * from employee

create table user1(id int,user1 varchar,password varchar(10))
select * from user1

CREATE TRIGGER trigger1
On Employee
FOR INSERT AS
BEGIN
INSERT user1(id,user1,password)
SELECT id,name,name+123 FROM INSERTED
END

This trigger is not working correctly errors are there plz reply

This is where your error is
Expand|Select|Wrap|Line Numbers
  1. SELECT id,name,name+123 FROM INSERTED
  2.  
Your column "name" is a character and you're trying to add an integer on it. Enclosed the integer with quote. Try doing it like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT id,name,name+'123' FROM INSERTED
  2.  
-- CK
Feb 12 '08 #2

P: 95
create table employee(id int,name varchar,address varchar(10))
insert into employee values(1,'A','FBD')
select * from employee

create table user1(id int,user1 varchar,password varchar(10))
select * from user1

CREATE TRIGGER trigger1
On Employee
FOR INSERT AS
BEGIN
INSERT user1(id,user1,password)
SELECT id,name,name+'123' FROM INSERTED
END

Still Nt working. In this whenever the new record is added in employee table,it is automatically inserted in User table, I m right na.
When i execute statement select * from user1,
no records are displayed?
Feb 12 '08 #3

ck9663
Expert 2.5K+
P: 2,878
create table employee(id int,name varchar,address varchar(10))
insert into employee values(1,'A','FBD')
select * from employee

create table user1(id int,user1 varchar,password varchar(10))
select * from user1

CREATE TRIGGER trigger1
On Employee
FOR INSERT AS
BEGIN
INSERT user1(id,user1,password)
SELECT id,name,name+'123' FROM INSERTED
END

Still Nt working. In this whenever the new record is added in employee table,it is automatically inserted in User table, I m right na.
When i execute statement select * from user1,
no records are displayed?

Are you executing it as it is? If yes, it's because you created the trigger after the insert.

Try to re-arrange the code like this:

create table employee(id int,name varchar,address varchar(10))
Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER trigger1
  2. On Employee
  3. FOR INSERT AS
  4. BEGIN
  5. INSERT user1(id,user1,password)
  6. SELECT id,name,name+'123' FROM INSERTED
  7. END
  8.  
  9. create table user1(id int,user1 varchar,password varchar(10))
  10.  
  11. insert into employee values(1,'A','FBD')
  12.  
  13.  
  14. select * from user1
  15. select * from employee
-- CK
Feb 12 '08 #4

P: 95
Thx Its working...........
Feb 13 '08 #5

P: 95
CREATE TRIGGER trigger2
On Employee
FOR DELETE AS
BEGIN
Delete From User1 where id=1
END

delete from employee where id=2

CREATE TRIGGER trigger1
On Employee
FOR INSERT AS
BEGIN
INSERT user1(id,user1,password)
SELECT id,name,name+'123' FROM INSERTED
END

create table user1(id int,user1 varchar,password varchar(10))

insert into employee values(1,'A','FBD')
insert into employee values(2,'B','Delhi')
select * from employee
drop table employee

select * from user1

I m creating insert and delete triggers on two tables. In delete trigger i gave
Delete From User1 where id=1
It deletes the particular ID.
But i want that any Id deleted from Employee is also deleted from User.
Feb 13 '08 #6

ck9663
Expert 2.5K+
P: 2,878
Try:

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER trigger2
  2. On Employee
  3. FOR DELETE AS
  4. BEGIN
  5. Delete From User1 where id in (select ID from deleted)
  6. END
  7.  
Good luck.

-- CK
Feb 13 '08 #7

P: 95
Hey thx and sorry for asking the que again on triggers.

UPDATE TRIGGER-

CREATE TRIGGER trigger3
On Employee
FOR UPDATE AS
BEGIN
UPDATE user1 where id in (SELECT id FROM UPDATED)
END

ERROR- Incorrect syntax near the keyword 'where'.

Tell me one thing with triggers we just insert,update or delete records from table,or anything more can be done also with the help of triggers.

VERY VERY THX AGAIN
Feb 13 '08 #8

ck9663
Expert 2.5K+
P: 2,878
Check your UPDATE syntax. Here's the complete UPDATE syntax.

Here's more about TRIGGERS

Good Luck

-- CK
Feb 13 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.