اسنیپت‌ها، ترفندها و کدهای کاربردی

-- Create a table named MarketingTeamRoles
CREATE TABLE MarketingTeamRoles(
	Id INT PRIMARY KEY IDENTITY(1,1),    -- Unique ID for each role, auto-incremented
	RoleName NVARCHAR(255)               -- Name or title of the marketing role
)

-- Add a new column named Email to the MarketingTeam table
ALTER TABLE MarketingTeam
ADD Email NVARCHAR(255)

-- Remove the Email column from the MarketingTeam table
ALTER TABLE MarketingTeam
DROP COLUMN Email

-- Modify the Email column's data type and size to NVARCHAR(300)
-- (This assumes the column exists before executing)
ALTER TABLE MarketingTeam
ALTER COLUMN Email NVARCHAR(300)

-- Rename the column 'Email' to 'PhoneNumber' in the MarketingTeam table
EXEC sp_rename 'MarketingTeam.Email', 'PhoneNumber', 'COLUMN'

-- Delete the entire MarketingTeamRoles table and all its data
DROP TABLE MarketingTeamRoles

-----------------
-- Log table to record customer actions (e.g. insert)
CREATE TABLE CustomerLog(
	LogId INT PRIMARY KEY IDENTITY,
	CustomerId INT,
	[Action] NVARCHAR(255),
	ActionDate DATETIME DEFAULT GETDATE()
);

-- Trigger: logs a record after a new customer is inserted
create TRIGGER trg_AfterCustomerInsert
ON customers
AFTER INSERT
AS
BEGIN
	INSERT INTO CustomerLog(CustomerId, [Action])
	SELECT CustomerId, 'INSERT' FROM inserted;
END;

-- Test insert to fire the trigger
INSERT INTO customers (CustomerID,ContactName, City)
VALUES(88,'Mohammad', 'Shiraz');