SQL Server Performance Forum – Threads Archive
how to write a trigger
CREATE TRIGGER SOPN ON ChitietNKFOR INSERT, UPDATE
AS
IF (SELECT COUNT(*) FROM info P, INSERTED I
WHERE P.SoPN=I.SoPN)=0
BEGIN
PRINT ‘SoPN info’
ROLLBACK TRANSACTION
END
Would you please verify to see if something wrong with this code. When i run they have me an error..
pse post the Scenario,Table structure, actual requirement and the full error with description and number. the given information is not adequate… Madhu
Is your question related to 2005?
If yes, then you can even access virtual tables inside stored procedures.
Read more info in BOL for 2005
a print statement inside a trigger is pretty useless. who would read it? http://msdn2.microsoft.com/en-us/library/ms189799.aspx www.elsasoft.org
Never use PRINT in triggers. Perhaps if you’re debugging, but remove after test (do not even put /* */ around it – delete the words). If you leave a PRINT, your client app will be receiving data that it wasn’t expecting. This can cause any number of errors.
this is the structure of table: CREATE TABLE Info
(
SoPN char(5) PRIMARY KEY,
NgayPN datetime,
MaNCC char(5) FOREIGN KEY REFERENCES HoSoNCC(MaNCC)
)
CREATE TABLE ChitietNK
(
SoPN char(5) FOREIGN KEY REFERENCES PhieuNhapKho(SoPN),
MaHang char(5) FOREIGN KEY REFERENCES DanhMucHH(MaHang),
Dongia int,
Soluong int,
PRIMARY KEY (SoPN,MaHang)
) this is an error i have received:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint ‘FK__ChitietNK__SoPN__00551192’. The conflict occurred in database ‘NhapXuatHangHoa’, table ‘Info’, column ‘SoPN’.
The statement has been terminated.
Either you have a foreign key constraint on the Info table relating to the other table or there is a trigger on the Info which is doing something which relates to the other table thats violate a foreign key constraint. But as suggested do not use PRINT statement in the trigger, rather store the value to a temp. table and use another method to output the values from that table. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>