Using Triggers in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Triggers in SQL

Author

Topic

abi
Starting Member

6 Posts

Posted - 2008-11-15 : 06:41:11

Hi Friends,

I'm new to SQL.Please help me out to create a trigger in sql

I have two tables in two different databases in same server.

The two different databases are amschn and amsggn

In amschn there is a table called ams_attendance which has got four fields such as(ename,card_no,in_time and out_time)

In amsggn there is a table called ams_attendance which has got three fields such as(ename,card_no and attend_status)

Whenever there is a insert/update in
ams_attendance table in amschn database , it should also update the
ams_attendance table in amsggn also.while updating ams_attendance in amsggn attend_status should be 'p'

Is it possible to do it bcoz both the table has got different fields??

Any thoughts or suggestions are highly appreciated

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-15 : 06:55:45

CREATE TRIGGER [trg_Insertams_attendance ]
ON ams_attendance
AFTER INSERT
AS
BEGIN

INSERT INTO amsggn.dbo.ams_attendance (ename,card_no,attend_status)
SELECT ename,card_no,'P'FROM INSERTED

END

abi
Starting Member

6 Posts

Posted - 2008-11-15 : 07:19:15

Thanks for the quick response....but am gettinr error as string or binary data would be truncated

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-15 : 07:27:03

check your string columns length... Like if you have ename varchar(23) on one side and your ename has data with more then 20 characters but on target database table ename is varchar(10) only. Make your table structure equal as per field length on both sides.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-15 : 07:56:54

Maybe this:

CREATE TRIGGER [trg_upd] ON [dbo].[ams_attendance]
AFTER INSERT,UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM INSERTED)
AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO Campaign.dbo.ams_attendance (ename,card_no,attend_status)
SELECT ename,card_no,'P'FROM INSERTED
END

IF EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
UPDATE t
SET t.attend_status = 'P',t.ename = i.ename,t.card_no = i.card_no
FROM Campaign.dbo.ams_attendance t
INNER JOIN INSERTED i
ON i.card_no=t.card_no
END

END

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-15 : 08:04:47

sodeep thanx for correcting me.

abi
Starting Member

6 Posts

Posted - 2008-11-16 : 01:18:08

Thanks for all your answers guys..when i tried to create a trigger with above sql statements am getting error as 'Syntax error converting datetime from character string'

Both of the datatype in the table are same.....do i have to change something in the code??

abi
Starting Member

6 Posts

Posted - 2008-11-16 : 01:30:26

am really sorry guys..i messed up with the fields in both the table

The actual fields present here is e_code,day,in_time,out_time in ams_attendance in amschn
fields present in ams_attendance in amsggn is e_code,day,attend_status in ams_attendance in amsggn

Now its showing me a error like 'Syntax error converting datetime from character string'

Can you help me for one more time...extremely sorry for the confusion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-16 : 04:56:04

[code]CREATE TRIGGER [trg_upd] ON [dbo].[ams_attendance]
AFTER INSERT,UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM INSERTED)
AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO amsggn.dbo.ams_attendance (e_code,day,in_time,out_time)
SELECT e_code,day,in_time,out_time FROM INSERTED
END

IF EXISTS (SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
BEGIN
UPDATE t
SET t.day= i.day,t.in_time= i.in_time,t.out_time = i.out_time
FROM amsggn.dbo.ams_attendance t
INNER JOIN INSERTED i
ON i.e_code=t.e_code
END

END[/code]
make sure corresponding columns in both dbs are of same datatype.

abi
Starting Member

6 Posts

Posted - 2008-11-17 : 11:00:13

Thanks a lot for all your help guys...its working fine for me....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 11:18:58

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources